how to Write query in better way

Question

Hi I have added All parameter report dropdown using Union so my Dropdown with Employee names like this - 'ALL','Mark','Sam'..... select * from dept where emp=@emp this works great but when somebody selects 'ALL' then it is not dispalying any record because it is not having any employee name with all but practically i want to display all employye at that time.I know I can do with If else but is there any way in single query I can manage both?

Answer 1

One way of achieving this is as mentioned below. Assuming @emp would be declared and passed accordingly. 

SELECT	*
FROM	dept
WHERE	EMP =	CASE 
					WHEN @emp = 'ALL' THEN EMP
					ELSE	@emp
				END

Another way of writing it could be as below:

SELECT	*
FROM	dept
WHERE	(
			(
				@emp =	'ALL'
			AND	1 = 1
			)
		OR	(
				EMP = @emp
			)
		)

Thanks! 

Answer 2

You can try this:

IF (@Param = 'All')
BEGIN
 SELECT * FROM TableName
END
ELSE
 SELECT * FROM TableName WHERE Name = @Param
END

---

Shatrughna.

Answer 3

shatrugna/Deepak I alredy mentioned that i want without if  else

Answer 4

As you need to manage the output based on @emp passed, you need to include some kind of check.

Using CASE, you are not actually writing multiple queries. Rather you are setting what to equate to EMP column of dept - whether will it be EMP itself or @emp parameter - which gets decided based on what is being passed.

Hope this clarifies.

Thanks!

Answer 5

Alternatively, you could dynamically append WHERE clause within the report code (or front end) based on value selected in report and pass on the appropriate query to the server accordingly.

Thanks!

Answer 6

Take a moment to check any of the below mentioned blogs. The suggestion with IF ELSE is not a bad one for your case.

Do you use ISNULL(...). Don't, it does not perform - short blog by Denis Gobo
Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later) - long and comprehensive article by Erland Sommarskog 
Catch All Queries - short blog by Gail Shaw
Sunday T-SQL tip: How to select data with unknown parameter set Nice blog by Dmitri Korotkevitch

Option recompile

Option recompile discussion thread

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

select * from dept where @emp IN ('ALL', emp);