This is one of the good puzzle i heard from my Interview.
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=@Name
Question 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