Multiple search filter using stored procedure

Question

I already have a stored procedure for the search filter but it's complex and long, how do enhance the stored procedure code?

I have 3 search filters: group, key and label, these search filters are related to one another.

My stored procedure code:

IF (@group <> '' AND @key <> '' AND @label <> '')
BEGIN
    SET @statement =  
        @statement + ' WHERE ([group] LIKE ''%' + @group + '%'' AND [key] LIKE ''%' + @key + '%'' AND [label] LIKE ''%' + @label + '%'')'
END
ELSE IF (@group <> '' AND @key <> '') 
BEGIN
    SET @statement = 
        @statement + ' WHERE ([group] LIKE ''%' + @group + '%'' AND [key] LIKE ''%' + @key + '%'')'
END
ELSE IF (@key <> '' AND @label <> '') 
BEGIN
    SET @statement = 
        @statement + ' WHERE ([key] LIKE ''%' + @key + '%'' AND [label] LIKE ''%' + @label + '%'')'
END
ELSE IF (@label <> '' AND @group <> '') 
BEGIN
    SET @statement = 
        @statement + ' WHERE ([label] LIKE ''%' + @label + '%'' AND [group] LIKE ''%' + @group + '%'')'
END
ELSE IF (@group <> '') 
BEGIN
    SET @statement  
        = @statement + ' WHERE [group] LIKE ''%' + @group + '%'''
END
ELSE IF (@key <> '') 
BEGIN
    SET @statement  
        = @statement + ' WHERE [key] LIKE ''%' + @key + '%'' '
END
ELSE IF (@label <> '') 
BEGIN
    SET @statement  
        = @statement + ' WHERE [label] LIKE ''%' + @label + '%'''
END

How do I modify the code to be simpler?

What has this got to do with C# or .NET? Nothing. Tricking people into viewing irrelevant questions is not going to make you friends. — jmcilhinney
– jmcilhinney, Commented Nov 4, 2022 at 7:14
This would be just soooooo much simpler with the use of an ORM like Entity Framework .... — marc_s
– marc_s, Commented Nov 4, 2022 at 8:38

sanjay patel · Accepted Answer · 2022-11-04 07:19:47Z

2

  SET @statement =  
            @statement + ' WHERE  
( (@group<>'' and [group] LIKE ''%' + @group + '%''') or (@group='' and 1=1))
( (@key <>'' and [key] LIKE ''%' + @key + '%''') or (@key ='' and 1=1))
( (@label <>'' and [label] LIKE ''%' + @label  + '%''') or (@label ='' and 1=1))

Please try using above way using sql injection

answered Nov 4, 2022 at 7:19

sanjay patel

455 bronze badges

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

Comments

m-harding · Accepted Answer · 2022-11-04 09:07:40Z

I have used the following pattern in the past when creating stored procedures with input arguments that represent multiple optional search criteria. The advantage of this technique are

No dynamic SQL
The query is clean
The WHERE clause has multiple ANDs (no ORs) allowing SQL to use any indexes you may have on the search columns

This is only an example but hopefully you get the idea and it's something you can build on.

-- Procedure Input Arguments    
DECLARE 
    @IntExample INT = NULL
    ,@StringExample VARCHAR(50) = NULL;


-- Constants
DECLARE @MinInt32 INT = (-2147483648), @MaxInt32 INT = 2147483647;

-- Input Argument Validation
DECLARE @IdMin INT = @MinInt32, @IdMax INT = @MaxInt32;
IF (@IntExample IS NOT NULL)
BEGIN
    SET @IdMin = @IntExample;
    SET @IdMax = @IntExample;
END

DECLARE @DescFilter NVARCHAR(50) = '%';
IF (@StringExample IS NOT NULL)
BEGIN
    SET @DescFilter = '%' + @StringExample + '%';
END

-- Procedure Query
SELECT * 
FROM dbo.MyTable
WHERE
(
    Id BETWEEN @IdMin AND @IdMax
    AND [Description] LIKE @DescFilter
);

wnutt · Accepted Answer · 2022-11-04 14:58:16Z

0

Assuming that you're only building dynamic SQL for the WHERE clause's sake.

This one will probably perform best due to short circuiting

SELECT * 
FROM dbo.MyTable
WHERE 
    ((@group = '') OR ([group] LIKE '%' + @group + '%'))
AND ((@key   = '') OR ([key]   LIKE '%' + @key   + '%'))
AND ((@label = '') OR ([label] LIKE '%' + @label + '%'))

This will also work, but it will be heavier on resources and not as readable as the one above.

SELECT * 
FROM dbo.MyTable
WHERE 
    ([group] LIKE STUFF('%%', 2, 0, @group) )  
AND ([key]   LIKE STUFF('%%', 2, 0, @key)   )
AND ([label] LIKE STUFF('%%', 2, 0, @label) )

answered Nov 4, 2022 at 14:58

wnutt

5493 silver badges5 bronze badges

Collectives™ on Stack Overflow

Multiple search filter using stored procedure

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related