use Case statement to simplify WHERE clause of Sel

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

use Case statement to simplify WHERE clause of Sel

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2006-08-15 : 15:53:11

Hi,
I want to use Case statement to simplify WHERE clause of Select query. In other words, I want to use Case to combine 2 separate Select statements into 1 Select statement only and do not need to use IF… ELSE.

Details
I have a table “SchHtmlControl”

SchHtmlControlID Control Description
---------------- -------------------------------------------------- --------------------------------------------------
1 DropDownList input data using DropDownList
2 TextBox input data using TextBox
3 RadioButton option

My query batch:

declare @optionSchHtmlControl int
/* if @optionSchHtmlControl =1 then pick DropDownList from the table;
@optionSchHtmlControl = 2 then pick TextBox
@optionSchHtmlControl = 1000 then pick DropDownList and TextBox
*/
set @optionSchHtmlControl = 1

IF(@optionSchHtmlControl = 1000)
BEGIN
Select * from SchHtmlControl where SchHtmlControlID in (1,2)

END
ELSE
BEGIN
Select * from SchHtmlControl where SchHtmlControlID = @optionSchHtmlControl
END

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-15 : 16:18:26

Something Like

Select * from SchHtmlControl where 
	( (@optionSchHtmlControl = 1000 and SchHtmlControlID in (1,2))  
 	 or		
	  (@optionSchHtmlControl != 1000 and SchHtmlControlID = @optionSchHtmlControl))

Srinika

johnsql
Posting Yak Master

161 Posts

Posted - 2006-08-16 : 09:33:06

Srinika,
Good helps. Thanks so much.
BTW, OR operator can be used at where Case cannot be used?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-16 : 09:35:19

"Case" is used with a different perspective in T-SQL, than in VB or such languages
Refer BOL for Details

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources