CODEWITHSUNDEEP
sqlsql-servert-sql
Luciana Rodrigues
Luciana Rodrigues

Reputation: 29

null as parameter not working

I'm using two parameters in a where clause. When I pass both parameters they bring me the expected results. But when I pass NULL it doesn't work.

Here's an example of what I'm trying to do.

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;

When I pass 'Souza' AS @Par2 it returns me 3 rows. But when the @Par2 IS NULL doesn't return anything. And it should return the values from @Par1. Right?

Upvotes: 0

Views: 327

Answers (2)

Arsalan
Arsalan

Reputation: 744

by doing 

DECLARE @Par2 VARCHAR(20) = NULL

the Whole @SQL becomes 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;

because of OR you should check every possible way

when the Par1 or Par2 is null it does not add to where clause

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

If you are going to use parameters, be consistent:

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;

When you set either @Par1 or @Par2 to NULL, the resulting query string is NULL -- as you have defined it.

Upvotes: 3

Related Questions