CODEWITHSUNDEEP
sqlsql-server
kha
kha

Reputation: 20013

SqlServer Stored Procedure with optional arguments

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,

Upvotes: 0

Views: 283

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

Upvotes: 2

Jamiec
Jamiec

Reputation: 136104

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)

Upvotes: 3

Related Questions