CODEWITHSUNDEEP
sqlsql-servert-sql
vicky
vicky

Reputation: 251

sql search query with multiple optional search parameters

I have a query in sql 2008 where a use can select multiple optional parameters like these: 

@DateFrom
@DateTo
@UserType
@Customer
@User

What's the best / most performant approach to filtering based on all of these options?

Upvotes: 17

Views: 56739

Answers (3)

M.Ali
M.Ali

Reputation: 69494

you can write you WHERE Clause something like this.....

This is a quick fix but a bad approach,,,,,

SELECT ......
FROM TABLE
WHERE 
     (@UserType IS NULL OR UserType = @UserType)
AND
    (@Customer IS NULL OR Customer = @Customer)
AND
    (@User IS NULL OR User = @User)
AND
    (<Similarly Other Conditions>)

The proper way of writing a query like this should be using dynamic SQL with the use of sp_executesql 

-- Optional Variables

Declare @UserType   VARCHAR(10) = NULL
    ,   @Customer   INT = 123
    ,   @User       INT = 123
    ,   @Sql        NVARCHAR(MAX);


-- Build SQL Dynamically 

 SET @Sql = N'  SELECT *
                FROM TABLE_Name
                WHERE 1 = 1 '
          + CASE WHEN @UserType IS NOT NULL THEN
            N' AND UserType = @UserType ' ELSE N' ' END
          +  CASE WHEN @Customer IS NOT NULL THEN
            N' AND Customer = @Customer ' ELSE N' ' END
          +  CASE WHEN @User IS NOT NULL THEN
            N' AND User = @User ' ELSE N' ' END

-- Finally Execute SQL 

 Exec sp_executesql @Sql
                , N'@UserType VARCHAR(10) , @Customer INT , @User INT'
                , @UserType
                , @Customer
                , @User;

Upvotes: 15

Shantanu Gupta
Shantanu Gupta

Reputation: 21188

You can avoid using multiple IF ELSE condition and dynamic query by using this approach.

SELECT * FROM TBL
WHERE (@Name IS NULL OR Name = @Name) 
AND (@Age IS NULL OR Age = @Age)

Upvotes: 31

meziantou
meziantou

Reputation: 21337

You can use a dynamic query

SELECT @sql=
'SELECT DISTINCT [Test_Id], [Test].[Test_Name], [Test].[Test_Date]
    FROM [Test]
    WHERE (1 = 1)'

IF @start IS NOT NULL
    SELECT @sql = @sql + ' AND ([Test].[Test_Date] >= @start)'
IF @end IS NOT NULL
    SELECT @sql = @sql + ' AND ([Test].[Test_Date] <= @end)'

SELECT @paramlist = '@start datetime, 
    @end datetime'

EXEC sp_executesql @sql, @paramlist,
    @start, 
    @end

You can also use different queries but if you have more than 2 parameters it's very annoying code

IF(@start IS NULL)
    IF(@end IS NULL)
        SELECT ...
    ELSE
        SELECT ...
ELSE
    IF(@end IS NULL)
        SELECT ...
    ELSE
         SELECT ...

Upvotes: 3

Related Questions