Where query with NULL parameter

Question

Why this is not possible in SQL Server?

I am getting:

Incorrect syntax near '@pp'.

SQL:

declare @pp INT = null

select * 
from ExtraExaminationsReportView  
where [PayerId] is @pp

You cannot parameterize keywords, you can try to make your query dynamic instead — BytesOfMetal
– BytesOfMetal, Commented Aug 18, 2016 at 8:38
why do you want to do it this way? you cannot change the @pp to e.g. 4. Your query would be where [PayerId] is 4 which is definitly wrong. — Matthias Burger
– Matthias Burger, Commented Aug 18, 2016 at 8:48

Tanner · Accepted Answer · 2016-08-18 09:00:16Z

The syntax you wrote won't work for a few reasons.

Firstly, if @pp was an int, the IS @PP syntax is incorrect, IS syntax is used in conjunction with NULL:

SELECT * FROM TABLE WHERE COL IS NULL

You can't do:

SELECT * FROM TABLE WHERE COL IS 1

You'd need to use = when checking for a numeric value:

SELECT * FROM TABLE WHERE COL = 1

So to cater for both a NULL value or an INT value, you can use an OR in the WHERE clause to check the value like so:

DECLARE @pp INT = NULL  -- you can set this to 1 to show it works for both

-- temp table with a null value and an int value = 1
SELECT  1 val
INTO    #TMP
UNION ALL
SELECT  NULL

SELECT  *
FROM    #TMP
WHERE   ( @pp IS NULL
          AND val IS NULL
        )  
        OR ( @pp IS NOT NULL
             AND val = @pp
           )  

DROP TABLE #TMP

Modifying your code:

DECLARE @pp INT = NULL

SELECT * 
FROM ExtraExaminationsReportView  
WHERE   ( @pp IS NULL
          AND PayerId IS NULL
        )
        OR ( @pp IS NOT NULL
             AND PayerId = @pp
           )

ahmet · Accepted Answer · 2016-08-18 08:47:29Z

1

You have to change your query logic. The only way is:

declare @pp INT = null
select * from ExtraExaminationsReportView  where  ([PayerId] IS NULL OR [PayerId] = @pp)

answered Aug 18, 2016 at 8:47

ahmet

7341 gold badge10 silver badges30 bronze badges

1 Comment

Tanner Over a year ago

the issue with your query is that if @pp is a number, then you will also return rows where playerId is null, which may not be desired.

Collectives™ on Stack Overflow

Where query with NULL parameter

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related