Tuesday, March 1, 2011

MSSQL - IF or CASE statement within WHERE clause

For sure you can do dynamic sql statements on MSSQL by concatening strings and executing "EXEC @sqlString". What if you don't want to use that approach? Some people would prefer not to do it on dynamic sql especially when it's tedious to do. Here's what I found out when I was so lazy doing the dynamic sql.

Instead of doing dynamic sql like this:

SET @sql = "SELECT * from table1"
IF (@country IS NOT NULL)
BEGIN
    SET @sql += " WHERE country = " + @country
END
EXEC (@sql)

Here's an alternative for not doing it by dynamic query string:

SELECT * from table1
WHERE
( 1 =
( CASE
WHEN @country IS NULL THEN 1
WHEN (country = @country) THEN 1
ELSE 0
END
)
)

No comments:

Post a Comment