February 9, 2016 at 11:13 am
I have a stored procedure that runs some code. I have 3 tables that uses this sp. I want to delete the records from a specific table depending on the value of the parameter passed.
3 tables:
ClaimSummary_Winco
ClaimSummary_SSI
ClaimSummary_StaterBros
I tried:
Delete
Case @Client
When 'Winco' THEN ClaimSummary_Winco
When 'SSI' THEN ClaimSummary_SSI
When 'Stater' THEN ClaimSummary_Stater
END
February 9, 2016 at 11:26 am
You cannot. You should use IF statement to control the flow.
The CASE statement returns a result expression based on the input, so you couldn't make transactional operations in the result expression section of the command.
Igor Micev,My blog: www.igormicev.com
February 9, 2016 at 11:32 am
CASE is an expression; it evaluates to some value or another. It is not a control-of-flow statement.
For what you're wanting you should use IF to determine what DELETE statement to run.
IF @Client='WINCO'
DELETE ClaimSummary_Winco
--and so on for the others
Also, if you're just deleting all the rows in the table with no filter, then you might look into using TRUNCATE instead. Of course, if you rely on DELETE triggers or that table is the parent table in a foreign key relationship, then TRUNCATE won't really work for you.
Cheers!
EDIT: I see Igor already posted most of what I said, so +1 to his post 🙂
February 9, 2016 at 11:57 am
As mentioned by Jacob, you need three IF statements here, not a CASE.
IF @client = 1
delete table 1
else if @client = 2
delete table 2
The DELETE cannot take a parameter for the table name. This must be resolved at compile time.
You could use dynamic SQL, but this is not recommended.
February 9, 2016 at 9:58 pm
I think you have been provided good explanations of CASE and IF, and when you can use each. When you go further into what you are trying to do you should consider making the procedure that decides which client to delete data for call another stored procedure for each client. What I mean is, this outer proc that takes the client...
create proc dbo.delete_client_summary (@client_id varchar(50))
as
begin
if @client_id = 'WINCO'
begin
exec dbo.delete_client_summary_winco;
end
else if @client_id = 'JANCO'
begin
exec dbo.delete_client_summary_janco;
end
end
...should call a proc like this, specific for each client:
create proc dbo.delete_client_summary_winco
as
begin
delete dbo.ClaimSummary_Winco
end
As complexity of what you are doing for each client grows you will give the optimizer the best chance of picking a good execution plan. Not to mention it makes the code easier to follow and write unit tests for.
Here is some deeper reading for you: How to Confuse the SQL Server Query Optimizer[/url]. Jump down to the section on "When Control Flow Attacks" and read Option 3 as it pertains to your situation.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply