Using stored procedure parameter as a column name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using stored procedure parameter as a column name

Author

Topic

dr_cabel
Starting Member

1 Post

Posted - 2004-03-27 : 17:32:58

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?

@ColumnName
@Value

UPDATE MyTable
SET (@ColumnName) = @Value

Any help would be very much appreciated..
Chris

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-27 : 17:46:13

If you have time to read

[url]http://sqlteam.com/searchresults.asp?SearchTerms=dynamic&SUBMITs1=Search[/url]

I'd write a stored proc with IF statements to examine the column and execute the proper update.

Other choices: - CASE the UPDATE

UPDATE MyTable
SET
Col1 = CASE WHEN @Columnname = 'Col1' THEN @Columnname ELSE Col1 END ,
Col2 = CASE WHEN @Columnname = 'Col2' THEN @Columnname ELSE Col2 END ,
et. cetera

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-27 : 22:56:39

Basically, you have to do what Sam said or use dynamic SQL. Since dynamic SQL is processor intensive and a security risk, it's better to take the time to do it like he said. You could write a proc to automatically produce the entire statement for you. Then just cut and paste the statement into a procedure.

If you want to use dynamic SQL, you would do something like this:

DECLARE
@sql NVARCHAR(4000),
@column NVARCHAR(55),
@value NVARCHAR(55)

SELECT
@column = 'name',
@value = 'value'

SELECT @sql = '
UPDATE table1
SET ' + @column + ' = ''' + @value + ''''

PRINT (@sql) --Gives an example of what would run.
EXEC(@sql)

You would of course need to finish the statement.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources