Dynamic SQL - why variable stays empty

Question

I have problem with dynamic sql (yeah only one ;) )

Inside stored procedure I declared a local variable like this :

DECLARE @cmd nvarchar(max) Later in the code I have this line with a lots of input parameters my sp recieved (sorry for the mess didn't succeed in organizing it) :

SET @cmd = 'INSERT INTO dbo.' + @ArchiveTableName + '(' + @ArchiveFieldsName + ',' + @ArchiveEntityMainIdentifierField + ')
    SELECT ' + @EntityFieldsName + ',' + '' + @EntityMainIdentifierField + '' +
       ' FROM [' + @OrganizationName + '].[dbo].[' + @EntityName + '] ent
        left join dbo.' + @ArchiveTableName + ' arc on arc.' + @ArchiveEntityMainIdentifierField + ' = ent.' + @EntityMainIdentifierField + '
        where ent.' + @EntityMainIdentifierField + ' = ''' + @ParentId + ''' and arc.' + @ArchiveEntityMainIdentifierField + ' is null'

After I debugged it, I found something weird. After running this @cmd stays empty.

Can anyone please give me a clue of why this might be happening?

If one variable is NULL then the entire string will become NULL — Pரதீப்
– Pரதீப், Commented Jan 11, 2017 at 14:38

HoneyBadger · Accepted Answer · 2017-01-11 14:43:27Z

6

I bet one of your other variables is NULL. 'Something' + NULL = NULL. You can use CONCAT(sql server 2012+), it will turn NULL into an empty string. Better is to use COALESCE(sql server 2008+) as well (and make sure the essential variables aren't NULL).

edited Jan 11, 2017 at 14:43

answered Jan 11, 2017 at 14:39

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

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

Collectives™ on Stack Overflow

Dynamic SQL - why variable stays empty

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related