Dynamic SQL, Empty Variable, Formatting Issue?

Question

I am using dynamic SQL to build out some statements. Here is a truncated example of a stored proc UpdateFOO. When I debug this stored procedure, the problem is the @SQL variable I am declaring always stay empty! It is supposed to fill with the query. I suspect it has something to do with how I am formatting this, but I cant spot if its a bad formatting error.

CREATE PROC [dbo].[UpdateFOO] 
@TEST1 uniqueidentifier,
@TEST2 nvarchar(40),
@TEST3 nvarchar(50),
@TEST4 char(1),
@TEST5 nvarchar(20),
@TEST6 nvarchar(40),
@LINKED_SERVER_NAME nvarchar(max),
@DATABASE_NAME nvarchar(max)
AS 
    SET NOCOUNT ON 
    SET XACT_ABORT ON  


    BEGIN TRAN
    DECLARE @SQL nvarchar(max)
    SELECT @SQL = 'UPDATE [' + @LINKED_SERVER_NAME + '].[' + @DATABASE_NAME + '].[dbo].[SOME_TABLE]
     SET [TEST1]=' + '''' + convert(nvarchar(36), @TEST1) + '''' +', 
     [TEST2]=' + '''' +@TEST2 + '''' +',
     [TEST3]=' + '''' + @TEST3 + '''' +',
     [TEST4]='+ '''' + @TEST4 + '''' +',
     [TEST5]=' + '''' + @TEST5 + '''' +',
     [TEST6]=' + '''' + @TEST6 + '''' +
     ' WHERE [TEST1] =' + '''' + convert(nvarchar(36), TEST1 )+ '''' +
     + 'SELECT [TEST1] FROM 
     [' + @LINKED_SERVER_NAME + '].[' + @DATABASE_NAME + '].[Rev].[SOME_TABLE] 
     WHERE [TEST1] =' + '''' + convert(nvarchar(36), TEST1 )+ '''' +''
     PRINT LEN(@SQL)
    EXEC (@SQL)
    COMMIT

TIA Experts!

Since you are concatenating, check to make sure each variable has a value. NUll concatentated to anything else is null — HLGEM
– HLGEM, Commented Mar 20, 2014 at 19:55
There's probably a NULL value somewhere in that SELECT @SQL = ... statement. NULL concatenated with anything yields NULL. Try using PRINT to check the values of @TEST2, @TEST3, etc. — Fuck StackOverflow
– Fuck StackOverflow, Commented Mar 20, 2014 at 19:56
indeed, it has a null value! What is the easiest way to deal with nulls in this situation? — user3010406
– user3010406, Commented Mar 20, 2014 at 19:58
Is it possible to get an example of how to use these in the above context, like the line:[TEST2]=' + '''' +@TEST2 + '''' +', becomes what? — user3010406
– user3010406, Commented Mar 20, 2014 at 20:06

John · Accepted Answer · 2014-03-20 20:08:21Z

1

Here's how you use ISNULL. If the first value is null, it will return the 2nd value.

SELECT @SQL = 'UPDATE [' + @LINKED_SERVER_NAME + '].[' + @DATABASE_NAME + '].[dbo].[SOME_TABLE]
     SET [TEST1]=' + '''' + convert(nvarchar(36), ISNULL(@TEST1, '')) + '''' +', 
     [TEST2]=' + '''' +ISNULL(@TEST2, '') + '''' +',
     [TEST3]=' + '''' + ISNULL(@TEST3, '') + '''' +',

...

etc...

answered Mar 20, 2014 at 20:08

John

1865 bronze badges

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

Comments

meziantou · Accepted Answer · 2014-03-20 21:07:26Z

0

You should use parametrized query. Here's an example:

DECLARE @sql nvarchar(max), @paramlist nvarchar(max)

SELECT @sql= 'UPDATE Table
              SET Col1 = @Value1,
                  Col2 = @Value2
              WHERE (1 = 1)'

SELECT @paramlist = '@Value1 nvarchar (256), @Value2 nvarchar (256)'
EXEC sp_executesql @sql, @paramlist, @Value1, @Value2

answered Mar 20, 2014 at 21:07

meziantou

21.5k7 gold badges73 silver badges89 bronze badges

Collectives™ on Stack Overflow

Dynamic SQL, Empty Variable, Formatting Issue?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related