CODEWITHSUNDEEP
sql-serverstored-proceduresparameter-passing
Afif Pratama
Afif Pratama

Reputation: 157

Create stored procedure with parameters which it has null values

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...

Upvotes: 2

Views: 437

Answers (1)

marc_s
marc_s

Reputation: 754230

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)

Upvotes: 3

Related Questions