Is there a CASE statement in TSQL, for logic flow?

[TroyMcClure]

I've seen examples of a CASE statment for Select statements, but I want to control logic flow, here's a simple pseudo code:

Select Case @DoWhat
Case 'update'
Update tblTest Set kang = 'testing';
case 'delete'
Delete from tblTest Where kang = 'testing';
case 'insert'
Insert into tblTest (kang) Values('hello');
case Else
--do something else
End Select

I've tried all manner of the above, with and without 'Select', using 'When'/'Then', etc, I've looked at examples, but everything seems to be within a context of returning a column--not controling logic flow.

How can I control logic like the above, without nesting If/Else's?

Thanks,
T

 

[hmckillop]

you need IF statement
e.g.

IF @DoWhat = 'Update'
BEGIN
Update tblTest Set kang = 'testing'
END
ELSE IF @DoWhat = 'Update'
BEGIN
  Delete from tblTest Where kang = 'testing';
END
ELSE
BEGIN
--do whatever else
END

But have a look at books online for exact syntax

"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLSister]

In the situation described above I would use IF statments.

If @DoWhat =  'update'
     Update tblTest Set kang = 'testing';
If @DoWhat =  'delete'
     Delete from tblTest Where kang = 'testing';
If @DoWhat =  'insert'
     Insert into tblTest (kang) Values('hello');
if@DoWhat = 'Other'
     --do something else

If you are going to need multiple steps in an IF statement the form would be:

If @DoWhat =  'update'
	Begin
    Update tblTest Set kang = 'testing'
	Select ID field from tablTest where kang = 'testing'
	End

Also note I removed the ; at the end fo the T-SQL statements as T-SQl doesn't use that.

Questions about posting. See faq183-874

 

[donutman]

If it's really like the example you posted, then I'd use separate SPs for each and have the calling routine decide. If statements in a SP might (not sure of this) make it impossible for the optimizer to do a good job on the pre-compile.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[TroyMcClure]

Thank you all for your input. So there is no true 'Select Case' in Tsql. Hopefully it may come in an upgrade...until then I guess I'll use the If blocks.

Donutman, that's a good point about the optimizer, I'm going to have to look deeper into that because the primary reason for what I'm doing is to re-write an SP that currently uses a bunch of If/Else blocks to create dynamic sql. I'm hoping to improve performance by having a fully compiled/optimized sp that can shave a few milliseconds off the current response time.

The example I posted was just an example for simplicity, it's really a complicated WHERE clause for an insert to a temp table then another Where clause for several Select's.

For example, I want to select a list of employees based upon either EmpID (unique), or all emps from a Dept, or from a CompanyCode, or belonging to a particular Union, and several other parameters. Each param may or may not be exclusive--I could pass just Dept, or Dept and Union, or any combination. This makes my If/Else logic have (currently) around 2 dozen possible combinations--there are some combinations that either don't make logical sense or aren't otherwise useful that I dissallow at the application level.

Currently I'm dynamically creating the WHERE clause and using Exec to run the final query string, but of course the sp isn't optimized because it can't see the actual sql until runtime.
However, in the past, I'd also tried using the 'infinite range' for unused params, ie:

WHERE EmpID >= @EmpIDLo AND EmpID <= @EmpIDHi
AND Dept >= @DeptLo AND Dept <= @DeptHi ...etc.

This allowed me to just pass in the 'Lo' params--and if @EmpIdLo was Null, then @EmpIDLo became 0 and @EmpIdHi became, 9999999, but if it had a value, then @EmpIDLo and @EmpIDHi were made equal. Same for all the other params. This way the SQL is not dynamic--just the param values are, so it's compiled--but it seems to me that the above example wouldn't use the Dept index if just Dept was passed. Am I on the right track here? Thanks for any input,
--T

 

[donutman]

You will improve the apps performance by eliminating the dynamic SQL. It definitely cannot be optimized in advance, so you lose one of the benefits of a SP. Give some thought to having the application make the decisions.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[TroyMcClure]

donutman,
Thanks, the peformance did increase. I do a GetTickCount() before the ADO .Execute and after to count milliseconds. This is gross execution time--which includes sending the statement to the server and returning values, which for me is what I want to measure. It was about 190 ticks prior to implementing the IF/Else (where it was using dynamic sql), and now it's about 140 ticks.

I think that's pretty good speed increase, since the procedure does many other things after this whole sql thing--including step through a cursor based on one of the formerly-dynamic sql statements.

I know--the next thing to improve performance will be to get rid of the cursor operation...but that's down the road.
--T

 

[donutman]

That should be at the top of the mountain! You hadn't mentioned that you also were using a cursor...where's the UDF and trigger?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[TroyMcClure]

Well, I wish I could get rid of the cursor here, but it seems unavoidable for now. However, the dynamic SQL was fetching records in the cursor loop, so each iteration the dynamic sql had to be parsed, compiled, etc., then executed. So the cursor loops through approx 100 records, and there were 2 (at least 2--possibley 3 or more depending on the state of certain fields in the current cursor record) sql statements on each cursor record, which now don't need to be parsed or compiled anymore.

I do have a trigger on the customer that writes to an audit table, and the audit table has a trigger that writes back to the customer table to mark it as updated, which triggers the audit table again, which triggers the update to the customer table...HaHa, just kidding--no I haven't gone the trigger route, I've got enough slowness with the cursor as it is. Thanks again for the help, and I think in a few months I may have the cursor-monkeys out of my program, but for now, baby-steps.
--T

 

[donutman]

Hehe, you had me going...I'm a slow reader.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]