CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user3281388
user3281388

Reputation: 267

SQL Stored Procs Null Parameters for a search of item

I am creating a SQL stored procedure that will be used for the search of an item. I am allowing the user to search by item code or UPC. Thus, I need to be able to allow my parameters to be null. I think I'm close, however, when I execute this stored procedure it returns too many results when I am expecting only one.

Here's what I have: 

  @itm_ItemCode int,
  @itm_UPC varchar(11)

SELECT 
     ITEM_CODE as itm_itemcode 
    ,UPC_CODE as itm_upc
    ,[DESCRIPTION] as itm_description
    ,BASE_PRICE as itm_regularcost

FROM ITEM_TABLE 
    where (ITEM_CODE = @itm_ItemCode or @itm_ItemCode IS NULL) 
          OR (UPC_CODE = @itm_UPC or @itm_UPC IS NULL)

Here are a few examples of what I have for my EXEC statement when I try to test my stored procedure: (Is this how I'm suppose to write my EXEC statements?

EXEC stp_ItemSearch null, 71591010583
EXEC stp_ItemSearch 300, null

Thank you for your help!

P.S. I'm using SQL Server Manager

Upvotes: 1

Views: 48

Answers (1)

podiluska
podiluska

Reputation: 51494

You mean and, not or

 where (ITEM_CODE = @itm_ItemCode or @itm_ItemCode IS NULL) 
      AND (UPC_CODE = @itm_UPC or @itm_UPC IS NULL)

Both of your examples have one parameter as null, so will return all rows.

If Item_code and upc_code are not nullable, then your code can be shortened to

where (ITEM_CODE = ISNULL(@itm_ItemCode,ITEM_CODE ))
      AND (UPC_CODE = ISNULL(@itm_UPC,UPC_CODE ))

Upvotes: 2

Related Questions