空作为参数无效

Question

我在where子句中使用两个参数。当我通过这两个参数时，它们会给我带来预期的结果。但是当我传递NULL时，它不起作用。

这是我想做的事情的一个例子。

CREATE TABLE #Teste (Par1 VARCHAR(20), Par2 VARCHAR(20), Par3 VARCHAR(20), Par4 VARCHAR(20))

INSERT INTO #Teste
SELECT 'Luciana', NULL, '123456', 'SP'
UNION
SELECT 'Henrique', 'Souza', NULL, 'RJ'
UNION
SELECT NULL, NULL, '768949', 'BA'
UNION
SELECT 'Luis', 'Alberto', '123456', NULL

DECLARE @Par1 VARCHAR(20) = 'Lu'
DECLARE @Par2 VARCHAR(20) = NULL
DECLARE @SQL NVARCHAR(MAX) = ''


SET @SQL = 

'SELECT * FROM #Teste WHERE (Par1 LIKE ''%' + @Par1 + '%'' OR @Par1 IS NULL) 
 OR (Par2 LIKE ''%' + @Par2 + '%'' OR @Par2 IS NULL)'

 EXEC sp_executesql @SQL, N'@Par1 VARCHAR(20), @Par2 VARCHAR(20)', @Par1, @Par2;

当我将'Souza‘作为@Par2 2传递时，它会返回3行。但是，当@Par2 2为NULL时，不会返回任何内容。它应该返回@Par1中的值。对吗？

Answer 1

如果要使用参数，请保持一致：

SET @SQL = '
SELECT *
FROM #Teste
WHERE (Par1 LIKE ''%'' + @Par1 + ''%'' OR @Par1 IS NULL) OR
      (Par2 LIKE ''%'' + @Par2 + ''%'' OR @Par2 IS NULL)
'

EXEC sp_executesql @SQL,
     N'@Par1 VARCHAR(20), @Par2 VARCHAR(20)',
     @Par1, @Par2;

当您将@Par1或@Par2设置为NULL时，生成的查询字符串是NULL --正如您定义的那样。

Answer 2

通过做

DECLARE @Par2 VARCHAR(20) = NULL

整个@SQL变成NULL

    SET @SQL = 

'SELECT * FROM #Teste WHERE 1=1 ' 
+ CASE WHEN @Par1 IS NOT NULL AND @Par2 IS NOT NULL THEN
' AND (@Par1 LIKE ''%' + @Par1 + '%''' 
+  ' or @Par2 LIKE ''%' + @Par2 + '%'')' 
ELSE
+ CASE WHEN @Par1 IS NOT NULL THEN ' AND @Par1 LIKE ''%' + @Par1 + '%'''  ELSE '' END 
+ CASE WHEN @Par2 IS NOT NULL THEN ' AND @Par2 LIKE ''%' + @Par2 + '%'''  ELSE '' END 
end

EXEC sp_executesql @SQL,
 N'@Par1 VARCHAR(20), @Par2 VARCHAR(20)',
 @Par1, @Par2;

因为有了OR，您应该检查所有可能的方法

当Par1或Par2为空时，它不会添加到where子句中。