SQL Server Stored Procedure - Filter by Parameter

NachitoMax 411 Reputation points
2021-07-01T15:22:16.263+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,851 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-07-02T02:34:51.937+00:00

    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.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. NachitoMax 411 Reputation points
    2021-07-01T16:04:46.65+00:00

    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

    0 comments No comments

  2. Tom Phillips 17,716 Reputation points
    2021-07-01T17:34:57.287+00:00

    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.

    0 comments No comments