July 18, 2013 at 6:13 pm
Hi everyone,
We have a SP that is quite long and it has a lot of such code:
UPDATE table1
SET col1 = CASE
WHEN ( somecolumn = 'abc'
AND somecolumn2 > 1 ) THEN 123
WHEN ( somecolumn = 'abc'
AND somecolumn2 <= 1 ) THEN 234
ELSE 0
END,
col2 = CASE
WHEN ( somecolumn3 = 'abc'
AND somecolumn4 > 1 ) THEN 123
WHEN ( somecolumn3 = 'abc'
AND somecolumn5 > 1 ) THEN 234
ELSE 0
END
As you can see there are a lot of constants here (abc, 1), so if the requirements change and instead of abc, they want to test against bcd, we have to go through the code and make those changes in the code, so that's a maintenance nightmare. How would you handle this kind of code and changing requirements? Thanks in advance.
July 18, 2013 at 10:00 pm
You can do one thing.....
You can assign your constant value to a variable like this
Declare @var char(3) ='abc'
And put this variable in condition of your case statements.... So if the requirement changes you just need to change the value of the variable once.....
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 18, 2013 at 10:10 pm
Oh yes, Take a variable and compare it with your condition. U can also make it to your parameter so u can pass while executing SP.
July 19, 2013 at 8:44 am
You could store your 'constants' in a table so if your requirements change you edit the data in a table rather than modifying the procedure. You could also store multiple parameters sets in a table.
Air code to follow....
DECLARE @param1 int
DECLARE @param2 char(3)
SET @param1 =
(
SELECT param1value
FROM paramsets
WHERE
param = 'param1' AND
paramsetname = 'MyPriorityParameters'
)
SET @param2 =
(
SELECT param2value
FROM paramsets
WHERE
param = 'param2' AND
paramsetname = 'MyPriorityParameters'
)
EXEC myProc @param1,@param2
July 19, 2013 at 10:49 am
Thanks to everyone for your helpful input!!
Chrissy, your solution is very interesting, but since our SP is quite large, it probably will have a very large, confusing call to the SP.
The other solution is interesting too, gives us a chance to make a change in one location.
Just for my own knowledge, is there a way to do this by storing the constant values in a table?
July 19, 2013 at 10:55 am
My suggestion had the constants in a table.
CREATE PROCEDURE proc
AS
--Declare your parameters
DECLARE @param1 int
DECLARE @param2 char(3)
--Set you parameters by retrieving values from a table called paramsets
SET @param1 =
(
SELECT param1value
FROM paramsets
WHERE
param = 'param1' AND
paramsetname = 'MyPriorityParameters'
)
SET @param2 =
(
SELECT param2value
FROM paramsets
WHERE
param = 'param2' AND
paramsetname = 'MyPriorityParameters'
)
--continue on using the parameters
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply