SqlServer Stored Procedure with optional arguments

Question

We have a situation where a user can query by any (or all) search fields.

Imagine the following table:

CREATE TABLE t_searchable(
   Column1 DateTime,
   Column2 Varchar(50),
   Column3 SmallInt)

All three columns are indexed and none are Nullable.

Now the user is able to search by either a date range query (on Column1), a query on Column2 and Column3 or any combination of these.

I thought about 2 ways of doing this:

1st approach:

Create 7 different stored procedures:

- Column1
- Column2
- Column3
- Column1 and 2
- Column1 and 3
- Column2 and 3
- Column1 and 2 and 3

And have the client choose which SP to call depending on the arguments

2nd approach:

Create a singular SP with masses and masses of IF ELSE statements inside.

Both approaches are very cumbersome and are not future proof, in that if we add a new column in the future that we need to search on, it won't be easy to implement as it would involve either touching the client AND the server (in case of solution 1) or make the IF ELSE even more complicated than it is for solution 2.

My question is: Is there a better way to do this? I would like to ideally avoid writing a custom SQL statement in code as that might be against company policy (they prefer SPs to custom SQL executions) but the more I think about it, the more it looks to be unavoidable but I thought I'd ask the experts here instead (I'm not very good @ SQL).

Many thanks,

You can use Dynamic-SQL like here (omit part with temporary table) — Lukasz Szozda
– Lukasz Szozda, Commented Sep 28, 2015 at 8:32

Jamiec · Accepted Answer · 2015-09-28 08:35:55Z

3

You do this by having the parameters of the stored procedure be optional, providing null as the default, and checking the null-ness of the parameter as part of the select.

CREATE PROCEDURE MySearch
    @Column1 DATETIME = NULL,
    @Column2 VARCHAR(50) = NULL,
    @Column3 SMALLINT = NULL
AS
SELECT column1,column2,column3 
FROM t_searchable
WHERE (@Column1 IS NULL OR Column1 = @Column1)
AND (@Column2 IS NULL OR Column2 = @Column2)
AND (@Column3 IS NULL OR Column3 = @Column3)

answered Sep 28, 2015 at 8:35

Jamiec

136k15 gold badges143 silver badges202 bronze badges

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

2 Comments

kha Over a year ago

Thank you very much. Checking this now and will update as soon as I know if this solution is better than the dynamic SQL for my usecase (performance wise).

Jamiec Over a year ago

Non-dynamic solution will pretty much always perform better than building up sql dynamically

Felix Pamittan · Accepted Answer · 2015-09-28 11:06:30Z

2

What you're asking for is a catch-all-query.

CREATE PROCEDURE YourProcedureName
    @column1    DATETIME NULL,
    @column2    VARCHAR(50) NULL,
    @column3    SMALLINT NULL
AS

SELECT
    *
FROM t_searchable
WHERE
    (@column1 IS NULL OR Column1 = @column1)
    AND (@column2 IS NULL OR Column2 = @column2)
    AND (@column3 IS NULL OR Column3 = @column3)

Another way is to use dynamic sql and execute it using sp_executesql:

CREATE PROCEDURE YourProcedureName
    @column1    DATETIME NULL,
    @column2    VARCHAR(50) NULL,
    @column3    SMALLINT NULL
AS

DECLARE @sql NVARCHAR(MAX)

SET @sql =
'SELECT *
FROM t_searchable
WHERE
    1 = 1' + CHAR(10)
IF @column1 IS NOT NULL
    SET @sql += '   AND Column1 = @column1' + CHAR(10)

IF @column2 IS NOT NULL
    SET @sql += '   AND Column2 = @column2' + CHAR(10)

IF @column3 IS NOT NULL
    SET @sql += '   AND Column3 = @column3' + CHAR(10)

EXEC sp_executesql 
        @sql, 
        N'@column1 DATETIME, @column2 VARCHAR(50), @column3 SMALLINT',
        @column1,
        @column2,
        @column3

edited Sep 28, 2015 at 11:06

answered Sep 28, 2015 at 8:35

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

3 Comments

Jamiec Over a year ago

ooh beat you by 3 seconds!

Felix Pamittan Over a year ago

@Jamiec, you're quick! I added an alternative solution.

kha Over a year ago

Thank you very much! I'll try both approaches and do some performance analysis and select your answer as answer if the second one is a winner. Otherwise I'll have to respect the 3 seconds :).

Collectives™ on Stack Overflow

SqlServer Stored Procedure with optional arguments

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related