Passing Null values into Variable for Dynamic SQL and OPENQUERY-CodePudding

I have a query that I need to pass in variable values into a dynamic SQL/openquery query.

declare @week int = 14
declare @id varchar(10) = 10

declare @SQL varchar(MAX), @OQWeek varchar(2) = @Week, @OQID varchar(10) = @ID

SET @sql = N'SELECT * FROM OPENQUERY(SERVER1,''SELECT T.SID ,   T.AgentNo , T2.Duration
                                        FROM server1.DB1.Table1 T
                                        inner join server1.DB1.Table2 T2 on t2.SID=f.SID 
                    WHERE Week ='   @OQWeek   ' and  T2.SID= ISNULL('   @OQID   ',T2.SID) '   ''')'

exec( @sql)

It appears to function correctly if I entered a value into @id variable, however if I enter 'NULL' into the variable it just comes back with Commands completed successfully.

this is also the same when I use COALESCE() too.

How do you handle/pass through a null value as you would in a direct SQL query? The normal SQL version of the WHERE clause would usually look like this WHERE Week = @OQWeek and T2.SID= ISNULL(@OQID, T2.SID)

CodePudding user response：

If one of your variables, @OQWeek or @OQID have the value NULL, then the entire string will resolve to NULL, as 'Some String' NULL = NULL.

Handle the NULL outside of the dynamic statement. As you don't need the clause if the value is NULL just don't include it when it is. I assume this is only applicable to @OQWeek, as that's the variable you wrapped ISNULL around:

DECLARE @week int = 14,
        @id varchar(10) = 10

DECLARE @SQL nvarchar(MAX), --This was the wrong data type
        @OQWeek varchar(2)= @Week,
        @OQID varchar(10) = @ID,
        @CRLF nchar(2) = NCHAR(13)   NCHAR(10);

SET @SQL = N'SELECT * FROM OPENQUERY(SERVER1,N''SELECT T.SID, T.AgentNo , T2.Duration'   @CRLF  
           N'                                  FROM server1.DB1.Table1 T'   @CRLF    --do you really have a database called "Server1" on your instance "Server1"?
           N'                                       INNER JOIN server1.DB1.Table2 T2 on t2.SID=f.SID'   @CRLF   
           N'                                  WHERE Week = '   @OQWeek   --Careful, syntax like this is open to injection
           CASE WHEN @OQID IS NOT NULL THEN @CRLF   N'                                    AND T2.SID = '   @OQID ELSE '' END   ';'');'

--PRINT @SQL; --Your debugging best friend
EXEC sys.sp_executesql @SQL; --Don't use EXEC(@SQL), you can't parametrise it.