将Null值传递到Dynamic SQL和OPENQUERY的Variable中

Question

我有一个查询，需要将变量值传递到动态SQL/openquery查询中。

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)

如果我在@id变量中输入了一个值，那么它似乎可以正常工作，但是如果我输入"NULL"到变量中，它只返回Commands completed successfully.

当我使用COALESCE()时也是一样的。

如何像在直接SQL查询中那样处理/传递null值？WHERE子句的普通SQL版本通常如下所示WHERE Week = @OQWeek and T2.SID= ISNULL(@OQID, T2.SID)

Answer 1

如果其中一个变量@OQWeek或@OQID的值为NULL，则整个字符串将解析为NULL，即'Some String' + NULL=NULL。

处理动态语句之外的NULL。如果值为NULL，则不需要子句。我假设这只适用于@OQWeek，因为这是您将ISNULL封装在一起的变量：

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.