SQL SERVER 動態查詢與空值的 Like 語句

我正在嘗試使用可空參數執行 SQL 動態查詢LIKE。如果參數有值，則查詢成功執行。但如果參數為 NULL，則查詢中斷僅返回“命令已成功完成”而沒有任何值。

這是我的查詢：

DECLARE  @articleNo VARCHAR(100), @query varchar(8000)

SET @articleNo = NULL

SET @query = 
'SELECT AT.Id AS ArticleId, AT.[Description] AS ArticleName, AT.ArticleNo, AG.Id AS ArticleGroupId, AG.Name AS ArticleGroup, St.Quantity AS Stock, AB.[Key] AS Attribute, AB.[Value] AS AttributeVal   
   FROM [dbo].[AT] AS AT
   INNER JOIN [dbo].[AG] AS AG ON AT.ArticleGroupId = AG.Id
   LEFT OUTER JOIN [dbo].[AAT] AS AB ON AT.Id = AB.ArticleId
   LEFT OUTER JOIN (SELECT ArticleId, SUM(Quantity) AS Quantity FROM [dbo].[SE] GROUP BY ArticleId) AS ST ON AT.Id = ST.ArticleId
   WHERE 
   ('''+ CAST((@articleNo) AS varchar) +''' = '''' OR AT.ArticleNo LIKE ''%' + CAST((@articleNo) AS varchar) + '%'')'

   EXEC (@query)

試試這個

DECLARE @articleNo VARCHAR(100)
,@query VARCHAR(8000)

SET @articleNo = NULL
SET @articleNo = COALESCE(@articleNo, '') -- SET to a blank value if NULL, you can't concatenate a NULL value into the string like you were doing.
SET @query = 
'SELECT AT.Id AS ArticleId, AT.[Description] AS ArticleName, AT.ArticleNo, 
AG.Id AS ArticleGroupId, AG.Name AS ArticleGroup, St.Quantity AS Stock, AB.[Key] AS Attribute, AB.[Value] AS AttributeVal   
FROM [dbo].[Articles] AS AT
INNER JOIN [dbo].[ArticleGroups] AS AG ON AT.ArticleGroupId = AG.Id
LEFT OUTER JOIN [dbo].[ArticleAttributes] AS AB ON AT.Id = AB.ArticleId
LEFT OUTER JOIN (SELECT ArticleId, SUM(Quantity) AS Quantity FROM [dbo]. 
[StockEvent] GROUP BY ArticleId) AS ST ON AT.Id = ST.ArticleId
WHERE 
      (@articleNo = ''''   OR AT.ArticleNo LIKE ''%@articleNo%'') '
--DON't use EXEC (@query), you want to make sure dynamic sql is parameterized


EXEC sp_executesql @query
,@params = N'@articleNo VARCHAR(100)'
,@articleNo = @articleNo

引用自：https://dba.stackexchange.com/questions/204179