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?

CodePudding user response：

  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

CodePudding user response：

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

1. No dynamic SQL
2. The query is clean
3. 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
);