Select Case issue - SQL Server Forums

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

Transact-SQL (2000)

Select Case issue

Author

Topic

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2003-07-15 : 14:23:30

Hi,

Im trying to use the case statement to control which sql statement to execute.

Here is what im trying
Select case (Select Rtrim(diary) from uics where uic = @uic)
when '' then * from candmast where uic = @uic
else * from candmast where diary = (Select diary from uics where uic = @uic)
end

Is somnething like this possible?
thanks

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-15 : 14:34:46

No. There is no switch logic for flow control in t-SQL. There is a case statement but it is only used as a conditional operator inside expressions.

You will need to use IF...THEN...ELSE statement to accomplish what you want.

Jonathan
{0}

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-07-15 : 14:42:01

You need the CASE inside the WHERE clause, and a little more logic:

SELECT * FROM candmast
WHERE UIC =
CASE (SELECT diary FROM uics WHERE uic = @uic)
WHEN '' THEN @UIC ELSE UIC END
AND Diary =
CASE (SELECT diary FROM uics WHERE uic = @uic)
WHEN THEN diary ELSE (SELECT diary FROM uics WHERE uic = @uic) END

This could probably become more efficient with a variable for diary, since in the sub-query is being evaluated for each row in the Candmast table, which can be avoided:

DECLARE @diary VARCHAR(50)

SELECT @diary = diary FROM uics WHERE uic = @uic

SELECT * FROM candmast
WHERE UIC =
CASE @diary
WHEN '' THEN @UIC ELSE UIC END
AND Diary =
CASE @diary
WHEN THEN diary ELSE @diary END

Owais

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2003-07-15 : 15:22:02

well I only want one of those where statements to be evaluated. If i include the diary with the uic then my results will be limited. the evaluation is either single uic lookup or a diary code(group of uics) lookup.

Ill look at If then and see what i can do

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-15 : 20:09:11

quote:
You will need to use IF...THEN...ELSE statement to accomplish what you want.

typical used SQL salesman ... trying to sell the buyer more than they need ....

(the keyword THEN doesn't exist in t-sql)

I have used a variable below to eliminate needing to SELECT from the uic table twice.


DECLARE @diary varchar(100)    -- whatever will hold the diary field

SET @diary = (select rtrim(diary) from uics where uic = @uic)

IF @diary = ''
  SELECT * FROM candmast WHERE uic = @uic
ELSE
  SELECT * FROM candmast WHERE diary = @diary

- Jeff

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-07-16 : 03:23:00

quote:

well I only want one of those where statements to be evaluated. If i include the diary with the uic then my results will be limited. the evaluation is either single uic lookup or a diary code(group of uics) lookup.

Ummmm...no. Look at the code I posted, if @diary is empty the UIC for the row is compared to @UIC, else it is compared to itself which should always return true. Same logic for the diary as well.

SELECT * FROM candmast
WHERE UIC =
CASE @diary
WHEN '' THEN @UIC ELSE UIC END
AND Diary =
CASE @diary
WHEN '' THEN diary ELSE @diary END

But I think Jeff's solution is a lot cleaner and more readable. It will also prevent @diary being evaluated over and over again... so go for it