Introduction
If you are working with SQL SERVER then you are definitely going to need this article. Let me explain a bit briefly.
You have one table named [User] where, you are storing 3 type of user's data such as
- Clients data
- Employee data
- Admin data
Consider the case if you are asked to write a stored procedure that needs to fetch the respective user data. What will you do ? I can say most of the people will go for a If-Else condition to fetch the data.
But the Case expression will solve your problem in just simple way. Find it below....
Behind The Scene
First see if you want to fetch the client data, employee data and admin data in If-Else condition.
-- AUTHOR: Tapan kumar
-- CREATE DATE: --
-- PROJECT:
-- CREATED BY: Tapan kumar
-- MODIFIED BY:
-- MODIFICATION DATE: 28DEC2013
-- Last MODIFICATIONS :
-- DESCRIPTION/PURPOSE: Gets all user data according to filter type.
-- UNIT TESTING : [Getuseranddetailstosendexpirynotification]
-------------------------------------------------------
CREATE PROCEDURE [dbo].[Getuseranddetailstosendexpirynotification] --1
@FilterType INT
AS
BEGIN
-- @FilterType = 1 (will select clients only)
-- @FilterType = 2 (will select employees only)
-- @FilterType = 3 (will select admins only)
SET nocount ON;
IF @FilterType = 1 -- For selecting client data
BEGIN
SELECT id,
firstname,
lastname,
salary
FROM [user]
WHERE isclient = 1
AND .isdeleted = 0
END
ELSE IF @FilterType = 2 -- For selecting employee data
BEGIN
SELECT id,
firstname,
lastname,
salary
FROM [user]
WHERE isemployee = 1
AND .isdeleted = 0
END
ELSE IF @FilterType = 3 -- For selecting admin data
BEGIN
SELECT id,
firstname,
lastname,
salary
FROM [user]
WHERE isadmin = 1
AND .isdeleted = 0
END
END
This is how your stored procedure will look like. You need to write the select query 3 times.
But here comes the use of CASE expression to save your time. See the below stored procedure to do the same thing..
-- AUTHOR: Tapan kumar
-- CREATE DATE: --
-- PROJECT:
-- CREATED BY: Tapan kumar
-- MODIFIED BY:
-- MODIFICATION DATE: 28DEC2013
-- Last MODIFICATIONS :
-- DESCRIPTION/PURPOSE: Gets all user data according to filter type.
-- UNIT TESTING : [Getuseranddetailstosendexpirynotification]
-----------------------------------------------
ALTER PROCEDURE [dbo].[Getuseranddetailstosendexpirynotification] --1
@FilterType INT
AS
BEGIN
-- @FilterType = 1 (will select clients only)
-- @FilterType = 2 (will select employees only)
-- @FilterType = 3 (will select admins only)
SET nocount ON;
SELECT id,
firstname,
lastname,
salary
FROM [user]
WHERE 1 = CASE
WHEN @FilterType = 1
AND isclient = 1
AND .isdeleted = 0 THEN 1
WHEN @FilterType = 2
AND isemployee = 1
AND .isdeleted = 0 THEN 1
WHEN @FilterType = 3
AND isadmin = 1
AND .isdeleted = 0 THEN 1
ELSE 0
END
END
In the above procedure you can see how I have written a single select query that behave differently depending on the @filterType .
Here is how it behaves
- @filterType = 1, it will select the client data.
- @filterType = 2, it will select the employee data.
- @filterType = 3, it will select the admin data.
Hope this will be helpful to you.
Happy Coding...
ffff
ReplyDeletekkkk
Deletegggg
ReplyDelete