This is one of the good puzzle i heard from my Interview.
When value is passed through that variable then it should execute complete SELECT statement with WHERE Clause, if value is not passed then SELECT statement should execute without WHERE Clause.
Eg:
Consider there are 5 Records in that Employee Table
If we pass @Name='Anand' then it should return First Record with FirstName and LastName.
If we are not passing any value then obviously @Name is Null as it is declared as NULL, so it should return all records with FirstName and LastName.
Condition
You should never use two set of Select Query (Some people will say we can use IF Statement and can write two set of Queries, one with WHERE Clause and one without WHERE Clause-never do this).
When i went for an Interview, interviewer asked me one good Puzzle in SQL , Quite Simple but really very Good Puzzle.
Question:
Consider a Local Variable,
DECLARE @Name Varchar(50)=NULL
SELECT FirstName+ ' '+LastName FROM Employee WHERE FirstName=@NameQuestion is very simple,
When value is passed through that variable then it should execute complete SELECT statement with WHERE Clause, if value is not passed then SELECT statement should execute without WHERE Clause.
Eg:
Consider there are 5 Records in that Employee Table
| ID | FirstName | LastName | EmailId | ContactNo |
| 1 | Anand | Kumar | anand1@gmail.com | 9898989898 |
| 2 | Anderson | anderson@gmail.com | 9898989898 | |
| 3 | Arnold | schwarzenegger | arnold@yahoo.com | 9898989898 |
| 4 | David | Blaine | david@gmail.com | 9898989898 |
| 5 | Ranbir | Kapoor | Ranbir@gmail.com | 9898989898 |
If we pass @Name='Anand' then it should return First Record with FirstName and LastName.
If we are not passing any value then obviously @Name is Null as it is declared as NULL, so it should return all records with FirstName and LastName.
Condition
You should never use two set of Select Query (Some people will say we can use IF Statement and can write two set of Queries, one with WHERE Clause and one without WHERE Clause-never do this).
Thank you
3 comments:
I think this will work.
CREATE PROC [usp_EmployeeSelect]
@Name varchar(50) = NULL
AS
SELECT FirstName + ' ' + LastName
FROM [Employee]
WHERE [FirstName] like ISNULL(@Name, '%')
this will work but question is where itself should never execute if @Name is null
Post a Comment