Stored Procedure for multiple columns
-
A certain update involves eleven columns on a SQL Server 2000 database. The update is about 500 lines long and is executed via a different stored procedure for each column. (11 sprocs) I then run one stored procedure which executes the eleven stored procedures. (thus the update process involves a total of 12 sprocs) The syntax for each stored procedure is the same except for the column name. I would like to run one stored procedure that does all of the columns. (a total of 1 sproc) Perhaps using a variable for the column name, and then the variable gets updated with the next column name at the end of the updates and continues to update the column name until all of the columns have been updated. How can I do this?
-
you could also just provide 11 inputvariables for your update proc and off course some input variables for your predicates (where-clause).
(use the columnnames and match datatypes and length)
And perform just one update statement !
(in stead of 11)update yourtable
set yourcol1 = @inputvar_yourcol1
, yourcol2 = @inputvar_yourcol2
....
where yourkeycolumn = @inputvar_yourkeycolumn
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply