CODEWITHSUNDEEP
sql-serversql-server-2008-r2dynamic-sql
BICube
BICube

Reputation: 4681

Dynamic SQL string returns NULL

I am trying to construct a dynamic SQL statement and for some reason it is not returning the expected results. This is my query 

DECLARE @user_script AS VARCHAR(MAX);



SELECT  @user_script += 'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '
        + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1)
        + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name])
        + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name])
        + SPACE(1) + 'END; '
FROM    sys.database_principals AS rm
WHERE   [type] IN ( 'U', 'S', 'G' )
        AND rm.default_schema_name IS NOT NULL;
PRINT ( @user_script );

My problem is that the print statement is not returning anything which I think is because the @user_script variable is NULL.

To evaluate if my table is actually returning results I ran this query (exact copy of the above query without assigning it to a variable) and this query returns 10 rows 

SELECT 'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '
        + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1)
        + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name])
        + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name])
        + SPACE(1) + 'END; '
FROM    sys.database_principals AS rm
WHERE   [type] IN ( 'U', 'S', 'G' )
        AND rm.default_schema_name IS NOT NULL;

I tried replacing the function QUOTENAME() with quotes and SPCACE() with actual space but I got the same issue.

I finally checked to see if any of the returned results from the second query is NULL. But none of the rows was a NULL.

Thanks for your help

Upvotes: 0

Views: 1008

Answers (2)

Cortright
Cortright

Reputation: 1174

Try putting a 

SET @user_script = ''

on the line before your 

select @user_script += ....

Upvotes: 2

sbiz
sbiz

Reputation: 321

You can start the select with 

SELECT  @user_script =  ISNULL(@user_script, '') + ...

Upvotes: 0

Related Questions