SQL Filter Query

Question

I have a table with a number of fields in it. I am trying to create search filter in asp.net so the user can search by one or a combination of fields. So basically I want to create a single stored procedure that takes in 4 params and it will append the param to the WHERE clause if its not null...

TableExample has 4 columns, Col1 Col2 Col3 Col4

I am hoping there is way to do this with a single stored procedure instead of having to create one for each possible combination.

I was trying something like this, which isn't correct, but its what ive got thus far.

THANKS!

CREATE PROCEDURE [dbo].[Search]
    @Col1 int,
    @Col2 int,
    @Col3 int,
    @Col4 int
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT *

FROM
    [dbo].[TestTable]
WHERE
    1=1
    CASE      
     WHEN @Col1 IN NOT NULL
     THEN AND [Col1] = @Col1

     WHEN @Col2 IN NOT NULL
     THEN AND [Col2] = @Col2

     WHEN @Col3 IN NOT NULL
     THEN AND [Col3] = @Col3

     WHEN @Col4 IN NOT NULL
     THEN AND [Col4] = @Col4
    END

Answer 1

You'd have to use dynamic SQL to do it:

CREATE PROCEDURE [dbo].[Search]
    @Col1 int,
    @Col2 int,
    @Col3 int,
    @Col4 int
AS

DECLARE @SQL nvarchar(MAX)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SET @SQL = 'SELECT * 
              FROM [dbo].[TestTable]
             WHERE 1=1 '

IF @Col1 IS NOT NULL
SET @SQL = @SQL + ' AND Col1 = ''' + @Col1 + ''' '

IF @Col2 IS NOT NULL
SET @SQL = @SQL + ' AND Col2 = ''' + @Col2 + ''' '

IF @Col3 IS NOT NULL
SET @SQL = @SQL + ' AND Col3 = ''' + @Col3 + ''' '

IF @Col4 IS NOT NULL
SET @SQL = @SQL + ' AND Col4 = ''' + @Col4 + ''' '

exec sp_executesql @SQL

END

Keep in mind that there are dangers to this, including SQL injection, as well as a host of other permissions problems that can arise, since it's dynamic SQL, but it's the only way to accomplish this in the database layer. If you want to build you query at the application layer (in C#), you can defend against the SQL injection attacks much more thoroughly.

Some dynamic SQL links that might help you understand the drawbacks:

http://www.sommarskog.se/dynamic_sql.html http://slashstar.com/blogs/tim/archive/2006/10/12/The-Prevalence-and-Dangers-of-SQL-Injection.aspx

Answer 2

Use the fact that OR short circuits. I've assumed -1 is not a valid value.

CREATE PROCEDURE [dbo].[Search]
    @Col1 int = -1,
    @Col2 int = -1,
    @Col3 int = -1,
    @Col4 int = -1
AS
Begin
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT *

FROM
    [dbo].[TestTable]
WHERE
 (@Col1 = -1 OR [Col1] = @Col1)
and
(@Col2 = -1 OR [Col2] = @Col2)
and
(@Col3 = -1 OR [Col3] = @Col3)
and
(@Col4 = -1 OR [Col4] = @Col4)



END

Answer 3

You can do it with a method similar to what you have:

WHERE
  CASE
    WHEN @Col1 IS NULL THEN true
    ELSE [Col1] = @Col1
  END
AND
  CASE
    WHEN @Col2 IS NULL THEN true
    ELSE [Col2] = @Col2
  END
...

Or you can make it a lot simpler, although possibly less readable:

WHERE (@Col1 IS NULL OR [Col1] = @Col1])
  AND (@Col2 IS NULL OR [Col2] = @Col2])
  AND ...

Answer 4

search is one of the rare options i preach using either dynamic sql or building your sql string in code. if you have an all sproc environment use dynamic sql in your sproc. parametrize it and use sp_executeSQL to run it to avoid SQL Injection

Answer 5

I thank you all for your replies. However, I did it a little bit differently. I hope it helps someone out! Here's how I went about it:

CREATE PROCEDURE [dbo].[TestTable_Search]
    @Col1 int,
    @Col2 uniqueidentifier,
    @Col3 datetime,
    @Col4 datetime
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT *

FROM
    [dbo].[TestTable]
WHERE
    [Col1] = COALESCE(@Col1, Col1) AND
    [Col2] = COALESCE(@Col2, Col2) AND
    [Col3] >= COALESCE(@Col3 + "00:00:00", Col3) AND
    [Col4] <= COALESCE(@Col4 + "23:59:59", Col4)