CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
DevFresh
DevFresh

Reputation: 1

Multiple search filter using stored procedure

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?

Upvotes: 0

Views: 1579

Answers (3)

wnutt
wnutt

Reputation: 549

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) )

Upvotes: 0

m-harding
m-harding

Reputation: 1

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
);

Upvotes: 0

sanjay patel
sanjay patel

Reputation: 45

  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

Upvotes: 2

Related Questions