March 27, 2004 at 3:38 pm
I'm tryin to create a general stored procedure which will update one of 140 columns, depending on the column name passed as a parameter. I was wondering if anyone else had tried this, or whether it is actually possible?
I cant seem to get it to use the value of the parameter..
@ColumnName
@Value
UPDATE MyTable
SET (@ColumnName) = @Value
Any help would be very much appreciated..
Chris
March 28, 2004 at 9:47 am
Only thru dynamic SQL.
Numeric types work this way or you can use String type example for both.
DECLARE @sql VARCHAR(1000)
SET @sql = 'UPDATE MyTable SET (' + @ColumnName + ') = ' + CAST(@Value as varchar(100))
EXECUTE (@sql)
String types (note most numerics will work due to implicit coversion but you will need to use CATS to force to varchar for concatitnation.
DECLARE @sql VARCHAR(1000)
SET @sql = 'UPDATE MyTable SET (' + @ColumnName + ') = ''' + @Value + '''
EXECUTE (@sql)
March 29, 2004 at 1:20 am
Or you can use parameterization to achieve the same tadk. Here is an example from BOL:
sp_executesql supports the setting of parameter values separately from the Transact-SQL string:
DECLARE @IntVariable INTDECLARE @SQLString NVARCHAR(500)DECLARE @ParmDefinition NVARCHAR(500)/* Build the SQL string once. */SET @SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'/* Specify the parameter format once. */SET @ParmDefinition = N'@level tinyint'/* Execute the string with the first parameter value. */SET @IntVariable = 35EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable/* Execute the same string with the second parameter value. */SET @IntVariable = 32EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariableI find this to be easier to work with, and it avoids having to use CAST for numeric values.Hope this helps,Regards,Morpheus
"See how deep the rabbit hole goes..."
March 29, 2004 at 1:30 pm
Actually, there are other solutions; they just aren't necessarily any better, and might even be worse.
Note, pseudo-code ahead:
create procedure take1 @field_to_update, @value
update table A
set field1 = case WHEN @field_to_update = 'field1' THEN @value ELSE field1 END
,field2 = case WHEN @field_to_update = 'field2' THEN CONVERT(int,@value) ELSE field2 END
...
Pros: presumably, the execution plan would remain the same each time, so no recompiles.
Cons: It's awfully ugly, and is potentially difficult to maintain.
Unknowns: Would this wind up being slower than the dynamic SQL alternative?
Love to see some feedback on this alternative.
R David Francis
March 29, 2004 at 1:51 pm
Actually you would need to add the WOTH RECOMPILE statement to your SPs declaration or most will suffer performance issues worse than Dynamic SQL. Otherwise should be similar in speed with the WITH RECOMPILE.
March 29, 2004 at 2:17 pm
I would opt for the dynamic SQL route. however you will have to cope with differing data types. Add this as a parameter. You then don't have to worry about 's in you text.
create procedure myupdate (@column varchar(100),@value varchar(100))
as
declare @sql nvarchar(100)
set @sql = 'Update myTable SET ' + @Column + ' = @Value'
exec sp_executesql @SQL, N'@Value varchar(100)' , @Value
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
March 29, 2004 at 5:05 pm
I'm curious as to why you say this. Of course, I am making the assumption that the mechanism used to identify which rows should be updated is essentially static, possibly identifying a single row via a primary key, or a set of rows via a piece of a logical primary key. Nothing in the original poster's request spoke to the issue of actually finding the data to be updated, just how to update it once it had been found.
Do execution plans varying significantly for on UPDATE depending on which columns are to be updated? My understanding has always been that execution plans deal with locating the rows the statement is working on, not so much what happens to those rows once they are located.
R David Francis
March 30, 2004 at 8:50 am
Doesn't seem worth doing. You have a table with 140 columns and you want to update each independently. The example you gave implies that you have one record in the table. Hard to tell, but that's what it appears to be.
If that is the case, you are using SQL in an inefficient way. Try this:
Create Table MyTable (
KeyName varchar(32) NOT NULL,
KeyValue varchar(48) NULL
March 30, 2004 at 8:52 am
for some reason, only about half of my previous message showed up (hmmm... bug?)
The intent was to show that a table with keynames and keyvalues is more efficient than a table containing a single row where only dynamic sql could be used to update it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply