Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Single Query Multiple Condition Puzzle

Puzzle Statement

Recently I got puzzle from a friend. What the puzzle says is You have to accept a parameter and use that in a where clause to get data from the table.

Now if the value of the parameter is NULL or blank then your query should return all the records from the table. If the value of the parameter is NON NULL and NON Blank then we should what ever data is matching to the value of the parameter. All these needs be handled in a single query.

Please check out the sample input values and expected output below.

Sample Input

Sample inputs are –

DECLARE @EmpID VARCHAR(2) = ”
DECLARE @EmpID VARCHAR(2) = NULL
DECLARE @EmpID VARCHAR(2) = 2

Expected Output

For ” Output will be –

DeptID EmpID EmpName Salary
1 1 Manju 10000.00
1 2 Raju 10000.00
2 1 Loka 10000.00
2 2 Anil 10000.00
1 3 Manju 10000.00
2 3 Raju 10000.00
2 4 Manju 10000.00
1 4 Jena 10000.00

For 2 Output will be –

DeptID EmpID EmpName Salary
1 2 Raju 10000.00
2 2 Anil 10000.00

For NULL Output will be –

DeptID EmpID EmpName Salary
1 1 Manju 10000.00
1 2 Raju 10000.00
2 1 Loka 10000.00
2 2 Anil 10000.00
1 3 Manju 10000.00
2 3 Raju 10000.00
2 4 Manju 10000.00
1 4 Jena 10000.00

Script

Use below script to create table and insert sample data into it.

--


Create Table EmpTable
(DeptID Numeric(2),
EmpID Numeric(2),
EmpName Varchar(10),
Salary Numeric(10,2))


Insert Into EmpTable Values (1,1,'Manju',10000)
Insert Into EmpTable Values (1,2,'Raju',10000)
Insert Into EmpTable Values (2,1,'Loka',10000)
Insert Into EmpTable Values (2,2,'Anil',10000)
Insert Into EmpTable Values (1,3,'Manju',10000)
Insert Into EmpTable Values (2,3,'Raju',10000)
Insert Into EmpTable Values (2,4,'Manju',10000)
Insert Into EmpTable Values (1,4,'Jena',10000)
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION 1 | Using OR

--

DECLARE @EmpID VARCHAR(2) = ''
SELECT DeptID,EmpID,EmpName,Salary FROM EmpTable WHERE CAST(EmpID AS VARCHAR(2)) = @EmpID OR @EmpID IS NULL OR @EmpID = ''

GO

DECLARE @EmpID VARCHAR(2) = NULL
SELECT DeptID,EmpID,EmpName,Salary FROM EmpTable WHERE CAST(EmpID AS VARCHAR(2)) = @EmpID OR @EmpID IS NULL OR @EmpID = ''

GO

DECLARE @EmpID VARCHAR(2) = 2
SELECT DeptID,EmpID,EmpName,Salary FROM EmpTable WHERE CAST(EmpID AS VARCHAR(2)) = @EmpID OR @EmpID IS NULL OR @EmpID = ''

GO


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements