Is there a way of conditionally selecting which WHERE-clause to use in an SQL query?-CodePudding

Knowing I have 2 variables declared called @Variable and @Name, I need something like this:

SELECT * from (myDbTable)
CASE WHEN (@Variable IS NOT NULL AND @Variable <> '')
THEN WHERE myDbTable.variable LIKE @Variable
ELSE WHERE myDbTable.name LIKE @Name

Basically, if the user enters a "variable" value, it should do a WHERE-clause on that variable, if not, it should do a WHERE-clause on Name.

Can I do this in SQL?

CodePudding user response：

This would do the work(Not the most efficient though):

SELECT * from (myDbTable)
WHERE ((@Variable IS NOT NULL AND @Variable <> '') AND myDbTable.variable LIKE @Variable)
OR ((@Variable IS NULL OR @Variable = '') AND myDbTable.name LIKE @Name)

You can also use dynamic sql like suggested in the comments.

CodePudding user response：

There are a few options here:

Use boolean logic

SELECT *
FROM dbo.myDbTable
WHERE (@Variable IS NOT NULL AND @variable != '' AND variable LIKE @Variable)
   OR ((@Variable IS NULL OR @Variable = '') AND [Name] LIKE @Name)
OPTION (RECOMPILE);

The RECOMPILE is added to the OPTION clause, as very likely the plans for the 2 variables could be very different. This means that a plan relevant to the valueof @variable (if it's NULL/'' or not) will be used.

Use `IF...ELSE` logic

IF NULLIF(@Variable,'') IS NOT NULL
    SELECT *
    FROM dbo.myDbTable
    WHERE variable LIKE @Variable;
ELSE
    SELECT *
    FROM dbo.myDbTable
    WHERE [Name] LIKE @Name;

Considering the simplicity of your query, this might be the "better" option, as the plans can be cached for the 2 different queries.

Dynamic SQL

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13)   NCHAR(10);

SET @SQL = N'SELECT *'   @CRLF  
           N'FROM dbo.myDbTable'   @CRLF  
           N'WHERE '   CASE WHEN NULLIF(@Variable,'') IS NOT NULL THEN N'variable LIKE @Variable'
                                                                  ELSE N'[Name] LIKE @Name'
                       END   N';';

EXEC sys.sp_executesql @SQL, N'@Variable varchar(50), @Name varchar(50)', @Variable, @Name; --Data types are guessed.

This, like the IF...ELSE solution will have cached plans, however, I feel that this is an over complication of what you have. If you don't understand the importance of well written dynamic SQL, and the dangers of poorly written dynamic SQL, you shouldn't be using this.

Use boolean logic

Use IF...ELSE logic

Dynamic SQL

Use `IF...ELSE` logic