CODEWITHSUNDEEP
sqlsql-serverdynamic-sql
Developer
Developer

Reputation: 428

Dynamic SQL - why variable stays empty

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?

Upvotes: 2

Views: 379

Answers (1)

HoneyBadger
HoneyBadger

Reputation: 15140

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).

Upvotes: 6

Related Questions