CODEWITHSUNDEEP
sqlsql-serverstored-proceduresif-statementswitch-statement
trysis
trysis

Reputation: 8426

SQL Server Stored Procedure WHERE Clause IF CASE No Value

I have a stored procedure with default values, which I set to NULL. In the WHERE clause, for each value specified, rather than NULL or not given (where it would also be NULL), I would like the column to equal this value, otherwise, I would like to search without this column being in the WHERE clause. The basic structure looks something like this:

// Set up the stored procedure
USE Table;
GO
CREATE PROCEDURE dbo.SearchTable

    // Specify value(s)
    @Name varchar(50) = NULL

AS

    // Set up the query
    IF(@Name IS NOT NULL
        SELECT * FROM Table WHERE Name=@Name;

    ELSE
        SELECT * FROM Table

BEGIN
END

I have more than 1 parameter, and so, with this logic, I would need 2 IF's for every parameter. As you can imagine, this gets boring, time-consuming, and error-prone fast.

I would like to move this logic into the query, preferably into the WHERE clause, but any way I have found will cause errors (besides exceptions, which would require just as many IF's). SQL Server doesn't like IF's in the WHERE clause as far as I know, and with CASE I would have to specify the column, which I do not want to do.

What should I do?

Edit: I have SQL Server version 2012, so please concentrate on this or any recent versions in your answer.

Upvotes: 0

Views: 2877

Answers (2)

amit_g
amit_g

Reputation: 31260

You could do something like this. The downside to this is that indexes may not be used properly and thus the performance may not be great.

SELECT * FROM Table
WHERE (@Name Is Null Or Name = @Name)
      And (@Col2 Is Null Or Col2 = @Col2)
      And (@Col3 Is Null Or Col3 = @Col3)

Each column condition is "anded". Or is used to apply that column condition only if @var is not null. So for example, if this is called with just @Name populated, it is equivalent to Where Name = @Name. If both @Name and @Col2 are populated, it is equivalent to Where Name = @Name And Col2 = @Col2.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you don't care about performance, you can do:

SELECT *
FROM Table
WHERE @Name is null or Name = @Name;

Often, having an or condition gets in the way of efficient use of indexes. Perhaps this isn't a problem in your case, though.

Upvotes: 3

Related Questions