Create stored procedure with parameters which it has null values

Question

I'm rookie in SQL Server.

I have a table dbo.Example which has 4 columns like this :

|Barcode|Goods |  Stock   |   Person |
-----------------------------------------
|0020307|Book  |Available |   Mike   |
|0090010|Table |NULL      |   Jane   |
|0140397|Chalk |NULL      |   Mike   |
|0190017|Glass |NULL      |Christoper|
|0080017|Hammer|Available |   Ron    |

I tried to create a stored procedure with parameters which it has null values in [Stock] column.

CREATE PROCEDURE examproc
    @person NVARCHAR(50) = NULL,
    @stock nvarchar(50) = NULL 
AS
    SELECT * 
    FROM dbo.Example
    WHERE person = ISNULL(@person, person)
      AND Stock = ISNULL(@stock, Stock) OR Stock IS NULL

And execute the procedure like this with parameter :

EXEC examproc @person = 'Mike'

But the result is not what I expected:

|Barcode|Goods |  Stock   |   Person |
-----------------------------------------
|0020307|Book  |Available |   Mike   |
|0090010|Table |NULL      |   Jane   |
|0140397|Chalk |NULL      |   Mike   |
|0190017|Glass |NULL      |Christoper|

I know there is something wrong with my SQL statement, and I'm still confused.

I need a suggestion..

Thanks...

marc_s · Accepted Answer · 2016-10-12 12:43:24Z

3

You need to be super careful when mixing AND and OR in a WHERE clause - try this:

CREATE PROCEDURE examproc
    @person NVARCHAR(50) = NULL,
    @stock nvarchar(50) = NULL 
AS
    SELECT * 
    FROM dbo.Example
    WHERE person = ISNULL(@person, person)
      AND (Stock = ISNULL(@stock, Stock) OR Stock IS NULL)

and you can further simplify that by just leaving out the second condition (Stock IS NULL) - that's already handled by the first stock condition:

CREATE PROCEDURE examproc
    @person NVARCHAR(50) = NULL,
    @stock nvarchar(50) = NULL 
AS
    SELECT * 
    FROM dbo.Example
    WHERE Person = ISNULL(@person, person)
      AND Stock = ISNULL(@stock, Stock)

answered Oct 12, 2016 at 12:43

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create stored procedure with parameters which it has null values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related