CODEWITHSUNDEEP
sqlselectstored-proceduresif-statementcase
Hiky
Hiky

Reputation: 81

In what scenarios should IF statements be used over CASE in SQL

I am writing a stored procedure to query a DB.

Parameters are passed to the procedure for the where clause.

I just can't seem to find anywhere a description of the pros and cons of SQL IF and CASE statements. Can anyone give me a reason why i should use one of these staements over the other or if im going in the wrong direction completely?

method 1:

DECLARE @SQL_WHERE_QUERY NVARCHAR(MAX) = '';
IF @SQL_PARAM IS NOT NULL SET @SQL_WHERE_QUERY = @SQL_WHERE_QUERY + ' AND [COLUMN_NAME] = '''+@SQL_PARAM+''''

method 2:

DECLARE @SQL_WHERE_QUERY NVARCHAR(MAX) = '';
SET @SQL_WHERE_QUERY = CASE WHEN @SQL_PARAM IS NOT NULL THEN ' AND [COLUMN_NAME] = '''+@SQL_PARAM+'''' END

Thanks

Upvotes: 0

Views: 334

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Your two code snippets are not the same.

The if version does not change the value of @SQL_WHERE_QUERY, because there is no else statement with a set. The case version sets the variable to NULL.

In the specific case of using if/else to set a variable value, the if and the case should be equivalent.

Otherwise, the two are quite different. if executes a block of code, so you can have begin/end statements, with arbitrarily complex T-SQL code. The case is used just for the expression and only allows constructs allowed in a select statement.

Personally, I would generally use if with set, and case in SQL statements.

Upvotes: 1

Related Questions