Using IF and Temp Table with variables, and BEGIN/END statements

user_3729714 0
Viewed 76

I'm trying to create a temp table based on an IF statement. I don't think I've used IF statements much, so I'm probably missing some logic. When I run the following code, I get an error message stating 'There is already an object named '#T1' in the database.'

What am I missing?

IF OBJECT_ID ('tempdb..#T1') IS NOT NULL DROP TABLE #T1;

BEGIN
    DECLARE @roleName AS VARCHAR(20) = 'role1';
    DECLARE @userName AS VARCHAR(20) = 'user1';
    DECLARE @schemaName AS VARCHAR(20) = 'schema1';

    IF @roleName = ''
        --OR   @userName IS NULL
        BEGIN

            SELECT      NEWID () AS ID
                      , dp.name  AS DB_PrincipalsName
                      , us.name  AS SysUsersName
            INTO        #T1
            FROM        sys.sysusers              us
            RIGHT JOIN  sys.database_role_members rm ON us.uid = rm.member_principal_id
            JOIN        sys.database_principals   dp ON rm.role_principal_id = dp.principal_id
            ORDER BY    us.name;
        END;
    ELSE
        BEGIN
            SELECT      NEWID () AS ID
                      , dp.name  AS DB_PrincipalsName
                      , us.name  AS SysUsersName
            INTO        #T1
            FROM        sys.sysusers              us
            RIGHT JOIN  sys.database_role_members rm ON us.uid = rm.member_principal_id
            JOIN        sys.database_principals   dp ON rm.role_principal_id = dp.principal_id
            WHERE       dp.name LIKE '%' + @roleName + '%'
            ORDER BY    us.name;
        END;

    SELECT  ID
          , DB_PrincipalsName
          , SysUsersName
    FROM    #T1;
END;
tsql sql
2 Answers

Specifying SELECT INTO causes the interpreter to attempt to create the table. In this case twice. You could try INSERT INTO instead. Something like this

IF OBJECT_ID ('tempdb..#T1') IS NOT NULL DROP TABLE #T1;
go
create table #T1(
    ID                              uniqueidentifier not null,
    DB_PrincipalsName               sysname not null,
    SysUsersName                    sysname not null);

BEGIN
    DECLARE @roleName AS VARCHAR(20) = 'role1';
    DECLARE @userName AS VARCHAR(20) = 'user1';
    DECLARE @schemaName AS VARCHAR(20) = 'schema1';

    IF @roleName = ''
        --OR   @userName IS NULL
        BEGIN
            INSERT INTO #T1
            SELECT      NEWID () AS ID
                      , dp.name  AS DB_PrincipalsName
                      , us.name  AS SysUsersName
            FROM        sys.sysusers              us
            RIGHT JOIN  sys.database_role_members rm ON us.uid = rm.member_principal_id
            JOIN        sys.database_principals   dp ON rm.role_principal_id = dp.principal_id
            ORDER BY    us.name;
        END;
    ELSE
        BEGIN
            INSERT INTO #T1
            SELECT      NEWID () AS ID
                      , dp.name  AS DB_PrincipalsName
                      , us.name  AS SysUsersName
            FROM        sys.sysusers              us
            RIGHT JOIN  sys.database_role_members rm ON us.uid = rm.member_principal_id
            JOIN        sys.database_principals   dp ON rm.role_principal_id = dp.principal_id
            WHERE       dp.name LIKE '%' + @roleName + '%'
            ORDER BY    us.name;
        END;

    SELECT  ID
          , DB_PrincipalsName
          , SysUsersName
    FROM    #T1;
END;
user_67487580

You will need to explicitly create your #T1 temp table before getting entering the IF scope, then your INSERTs should work as expected.

CREATE TABLE #T1
 (
  ID UNIQUEIDENTIFIER NOT NULL,
  DB_PrincipalsName SYSNAME NOT NULL,
  SysUsersName SYSNAME NOT NULL
 )

The optimizer doesn't necessarily follow instructions the same way a procedural language does, and may evaluate items in an unexpected order relative to when it creates them.

user_143298300
Related