Passing Null values into Variable for Dynamic SQL and OPENQUERY

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)

Solution

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.