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');
Answer by Matt Whitfield ·
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...
Answer by Grant Fritchey ·
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.