Use Case or split into four procedures?
Hi So, I have the SP below which updates the Exec_Code, based on the Policy Type. I'm wondering should I split this into four different procedures instead? So that I could have SP 1 as
SET Exec_Code = PC_MAP WHERE (PolType = PC) AND (PC_Map <> '0000')
Or should I get the same performance from using the Case statement as below?
CREATE PROCEDURE dbo.PersLines_ByDir
AS
SET NOCOUNT ON
UPDATE dbo.Monthly_Pre
SET Exec_Code = CASE PolType
WHEN 'PC' THEN
CASE
WHEN PC_Map = '0000' THEN Exec_Code
ELSE PC_Map
END
WHEN 'HC' THEN
CASE
WHEN HC_Map = '0000' THEN Exec_Code
ELSE HC_Map
END
WHEN 'HQ' THEN
CASE
WHEN HC_Map= '0000' THEN Exec_Code
ELSE HC_Map
END
WHEN 'HI' THEN
CASE
WHEN HI_Map= '0000' THEN Exec_Code
ELSE HI_Map
END
END
FROM blahblah
WHERE PolType In ('PC','HC','HQ','HI');
Best Answer
It's an it depends, I'm afraid. If poltype is indexed, then you may find that you get the best performance from four separate update statements, but if it is not then your case statement would reduce scanning. I don't see the need for four separate stored procedures, however, I would simply put four update statements in the ome procedure...
I would probably leave it as is, but I'd suggest testing it by passing different values to the procedure, but flush the cache between each call and check to see if the execution plan changes radically between each call. If there are no or only minor changes, you're probably OK, but if there are radical changes, possibly even scans, table spools, what have you, then you may need to break it up.
I would do it in one. I'm not a fan of IN because it generally uses a scan, but doing it with a single procedure should be at least as quick as four calls. CASE is plenty fast enough.
