I think every body know how to use the CASE in the select query.
Today i will explain use of CASE in the where condition with some specific logic triks.
Example :
DECLARE @Recordstatus AS BIT
SET @Recordstatus = 1
SELECT * FROM dbo.ADT_Columns
WHERE RecordStatus = (CASE @Recordstatus WHEN @Recordstatus THEN 0 ELSE 1 END)
if CASE clause returns some value then you use CASE in Where condition.
Another thing i will explain is logical use of case which remove the use of If condition and also use the optional parameter in procedure.
I think many developers know that feature but i think nobody uses this option in sqlserver for the procedure level we use this at .net or clientside mostly but it is very useful feature. When you use one procedure at many places then you need not to pass this parameter at every place.
In below example i will cover both the topics.
— Run below code in AdventureWorks DB
— GetAddress ‘All’
— Procedure with IF condition
CREATE PROCEDURE GetAddress @city VARCHAR(50)
AS
BEGIN
IF @city = ‘All’
BEGIN
— Returns all Records whithout any condition
SELECT * FROM Person.Address
END
ELSE
BEGIN
— Returns all Records which match the condition
SELECT * FROM Person.Address
WHERE City LIKE @City
END
END
— GetAddressWihtCase ‘London’
— GetAddressWihtCase ‘All’ or GetAddressWihtCase ‘All’
— Procedure without IF condition and Optional Parameters
CREATE PROCEDURE GetAddressWihtCase @city VARCHAR(50) = ‘All’
— OPtional parameter
AS
BEGIN
SELECT * FROM Person.Address
WHERE City LIKE (CASE @city WHEN ‘All’ THEN City ELSE @city END)
— Use it’s own value in the condition this is very useful thing and independent to
— Technology you can use in Oracle or in MySQl too.
END
I hope this article is useful to enhance your knowledge about Sqlserver.If you like this article
then please give comment on this article.
SQL HELPER 