September 23, 2008 at 8:42 am
Hi, I have two basic questions?
1) How do I pass a column name into a stored procedure?
2) Is this worth doing? Or will I lose the benefits of Stored procedures?
OK, here is how these questions apply to my unique situation:
I have a table of about 100,000 records, there are 13 columns in the table that get updated regularly (via a website), but always it is just one column at a time (i.e. one client can only update one column). The update is always very simple (simply add 1 to a numeric column).
Should I create one stored procedure to find a particular row in the table and update the one column? (passing in the column name and row ID?)
Or should I create 13 seperate stored procedures, one for each editable column in the table? (Passing in just the row ID)
September 23, 2008 at 9:03 am
If you wanted to pass the column name then you would want to use dynamic sql. Another option, and the one I use, assuming the web page has all the data for the updated row is just to pass ALL the columns to the sp every time and just include all the columns in the update statement like this:
[font="Courier New"]CREATE PROCEDURE dbo.test
(
@id INT,
@col1 VARCHAR(10),
@col2 VARCHAR(10)
)
AS
SET NOCOUNT ON
UPDATE TABLE
SET col1 = @col1,
col2 = @col2
WHERE
id = @id
END[/font]
The only reason not to do this is if you have a trigger that is using if update(column_name). Even if you don't have all the data for the row you can default all the parameters to null and use something like this:
[font="Courier New"]CREATE PROCEDURE dbo.test
(
@id INT,
@col1 VARCHAR(10) = NULL,
@col2 VARCHAR(10) = NULL
)
AS
SET NOCOUNT ON
UPDATE TABLE
SET col1 = ISNULL(@col1, col1),
col2 = ISNULL(@col2, col2)
WHERE
id = @id
END[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 23, 2008 at 9:36 am
You have lost me there on passing all the column names into the procedure. How would you tell it which one to change and which ones to leave the same?
As for just using Dynamic SQL....
Would it not be more efficent to just create 13 seperate stored procedures for each column name? In the long run, assuming that 1,000s of users are updating the database, it would be easier for SQL server to retrieve and update the records with 13 seperate stored procedures.
September 23, 2008 at 9:55 am
In the first code example you are required to pass all the parameters to the stored procedure so the web code would pass all the parameters and it updates all the columns, but the ones that have not changed would be updated using the existing values.
In the second code example you have provided defaults for all the parameters except id, so you would only pass in the one parameter that is changing, and all the Nulls would again be updated, but using the existing value.
Doing either of these does not really change performance as the locking needed is the same for any update as as best you would lock the entire row being update regardless of how many columns are updated. The benefit is that you only have to manage 1 update procedure for all updates on that table.
I'm not saying that these are the only ways or the best ways, I was providing you with options and my opinion on how to do it.
If you used dynamic sql and used the sp_executesql procedure you would still get benefits of cached execution plans. The only issue would be you would have 13 execution plans, just as you would would 13 procs, while with either of my solutions you have 1 execution plan.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 23, 2008 at 9:58 am
As anyone ever tested this thouroughly (the cost of passing all the parameters all the time, VS the cost of the nullif(isnull, Colname)) execution).
If you know that only 1 column can only ever be updated, then I'd just make a sp that does only that and optimize for that.
Now for the question of making 1 sp per column... that would create about 2000 extra sps in my current system... I don't see that it's worth the trouble... and for what gain really?
September 23, 2008 at 10:20 am
About 6 months ago I posted a question asking about update procs and if I should require all the params or go the IsNull route and the consensus on that thread was to pass all the params all the time. Here is the link: http://www.sqlservercentral.com/Forums/Topic479982-145-1.aspx
I have not tested, but I don't think there is much overhead in using the IsNull because I am using it against a parameter in the update list not in the Where so it should not affect index selection.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 23, 2008 at 10:34 am
In the second code example you have provided defaults for all the parameters except id, so you would only pass in the one parameter that is changing, and all the Nulls would again be updated, but using the existing value.
So to call a procedure with default values and you want to leave the default values do you leave the argument empty?
exec test , , , , , 8, , , ,
Like that?
Also this would only solve half of the problem, I would still need to know the current value in the field so I can add 1 to it. So I would first need another stored procedure to retrieve the value in the designated field I want to change.
September 23, 2008 at 10:41 am
You would need to pass parameters by name. Like this:
code]
Exec tableA_upd @id = 1, @col1 = 'Test'[/code]
Leaving any unchanged parameters out.
I also assume that you have the value(s) on the web page already. Since you are doing an update.
In .NET you would use a the command and parameter objects something like (psuedocode):
If txtCol1 has changed then
Parameter.name = "@col1"
Parameter.value = "Test"
-- include type, direction, size as needed
Command.Parameters.Add(Parameter)
Else
End If
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 23, 2008 at 11:44 am
No, no info is on the webpage, it is an anonymous posting of an event, the only thing the database does is keep track of the number of events posted.
But I think i have worked out the optimum solution, the procedure will have 13 variables for the field names and 1 variable for the unique record ID. The defaults for th field names will all be zero, then when I call the procedure it will specify to change one column to 1. It will then add the varoiable to all columns, but only one column's value will change (the one that has its variable changed from 0 to 1).
September 23, 2008 at 12:14 pm
Thanks for all the help Jack, your syntax examples were useful, the only thing is I had to remove you're "END" statement to get it to run without an error, not sure why??
Anyway, the procedure is done and working though i am still not 100% convinced that going through the pointless exercise of adding zero to 12 columns everytime it runs is the most efficient way of doing it.
I still think that maybe having 13 seperate procedures would be better??
September 23, 2008 at 12:30 pm
That's a completly different topic.
Can you post the DDL along with the requirement of the system?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply