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