Dynamic SQL string returns NULL

Question

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

I think you have to initialize @user_script or use = instead of +=. — Giorgos Betsos
– Giorgos Betsos, Commented Jun 4, 2015 at 20:36

Cortright · Accepted Answer · 2015-06-04 20:38:01Z

2

Try putting a

SET @user_script = ''

on the line before your

select @user_script += ....

answered Jun 4, 2015 at 20:38

Cortright

1,1746 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BICube Over a year ago

Yup. that's it. I am looking for a place to hide now. Thanks :)

sbiz · Accepted Answer · 2015-06-04 20:37:15Z

0

You can start the select with

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

answered Jun 4, 2015 at 20:37

sbiz

3213 silver badges10 bronze badges

Collectives™ on Stack Overflow

Dynamic SQL string returns NULL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related