SQL Server Stored Procedure - Filter by Parameter

Question

Hey

What am i doing wrong here? Its something ive done many times with more complicated statements but for some reason, this logic is working (at least for me).....

I am passing in 3 parameters and assigning each parameter with a default value of NULL
I am then selecting the fields from the table
Finally, i am using a WHERE clause to return the data based on the parameter values.

Here is my Stored Procedure

    ALTER PROC [dbo].[usp_tbl_PartListSelect]   
        @Type varchar(100) = NULL,  
        @Size varchar(100) = NULL,  
        @CategoryID int = NULL  
    AS   
     SET NOCOUNT ON   
     SET XACT_ABORT ON    
      
     BEGIN TRAN  
      
    SELECT        id, part_number, description, categoryID, size, type  
    FROM            dbo.tbl_PartList  
    WHERE        ([type] = @Type) OR @Type IS NULL AND ([size] = @Size OR @Size IS NULL) AND ([categoryID] = @CategoryID OR @CategoryID IS NULL)  
    COMMIT  

The problems i get.

1. If i call the Stored Procedure without any parameters, all rows are returned (this is correct)
2. If i call the Stored Procedure with only @Size, All rows at that size are returned (this is correct)
3. If i call the Stored Procedure with @Size AND @type , i expect to get rows back only of that size & type but i get all rows of Type with size being ignored

Id like to call this SP 3 ways

1. no Parameter values to get all rows returned
2. by @type & by @Size to get the size & type returned
3. by @type & by @Size & by @CategoryID so that i get all filtered rows only returned.

Examples
--[usp_tbl_PartListSelect]
return all table rows

--[usp_tbl_PartListSelect] 'FRAME'
return all table rows where type = FRAME - any row with FRAME is returned

--[usp_tbl_PartListSelect] 'FRAME', '4in'
return all table rows where type = FRAME AND size = 4in - any row with FRAME and 4in is returned

--[usb_tbl_PartListSelect] NULL, '4in'
return all table rows where type = NULL AND size = 4in - any row with 4in returned

--[usp_tbl_PartListSelect] 'FRAME', '4in', 2
return all table rows where type = FRAME AND size = 4in AND CategoryID = 2 - any row with FRAME and 4in and 2 is returned

Is there a specific way to order the WHERE clause for conditional parameter values?

Thanks

Answer 1

Hi @NachitoMax ，

Welcome to Microsoft Q&A!

is there a specific order in which to prioritize the criteria in the WHERE clause?

Per my knowledge, the answer of above question is NO.

You could refer below updated one based on yours which you could also execute this procedure successfully even when @Size is null.

ALTER PROC [dbo].[usp_tbl_PartListSelect]   
    @Size varchar(100) = NULL,  
    @Type varchar(100) = NULL,  
    @CategoryID varchar(100) = NULL  
AS   
SET NOCOUNT ON   
SET XACT_ABORT ON    
          
SELECT [id], [part_number], [description], [size], [type], [categoryID]  
FROM dbo.tbl_PartList  
WHERE ([size] = @Size OR @Size IS NULL) AND ([type] = @Type OR @Type IS NULL) AND ([categoryID] = @CategoryID OR @CategoryID IS NULL)  

You could validate above with more conditions and check whether it is also working.

If above is still not working, please post CREATE TABLE statements for your table together with INSERT statements with sample data. We also need to see the expected result after execution.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

This works good enough for what i need. Not much change, just the order in which i filter the rows and i removed the need to shop all rows

ALTER PROC [dbo].[usp_tbl_PartListSelect] 
     @Size varchar(100) = NULL,
     @Type varchar(100) = NULL,
     @CategoryID varchar(100) = NULL
    AS 
     SET NOCOUNT ON 
     SET XACT_ABORT ON  

     BEGIN TRAN

    SELECT        [id], [part_number], [description], [size], [type], [categoryID]
    FROM            dbo.tbl_PartList
    WHERE        ([size] = @Size) AND ([type] = @Type OR @Type IS NULL) AND ([categoryID] = @CategoryID OR @CategoryID IS NULL)
    COMMIT

Question remains though, is there a specific order in which to prioritize the criteria in the WHERE clause?

Thanks

Answer 3

In your first post your ) is in the wrong position.

([type] = @Type) OR @Type IS NULL AND 

Should be:

([type] = @Type OR @Type IS NULL) AND 

I am not sure what you mean in your 2nd post by "specific order in which to prioritize the criteria". The query you posted does all 3 of your tests successfully. All 3 parameters are optional.

Also, you do not need a begin tran/commit around a select statement.