selecting column name dynamically

Question

Post reply

selecting column name dynamically

simplykash

Valued Member

Points: 70
More actions
April 17, 2010 at 1:25 am

#220615

Hi all
I have a scenerio where i have to select a column dynamically to update..
its some thing like this
update dbo.tbl_data set var2= @toUpdate
where var1 =@selecteditem
here var1 and var2 are column names which i want to be dynamic...
thank in advance for your reply....

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

You can use Dynamic SQL for the same

DECLARE@strSQLVARCHAR(500)

DECLARE@toUpdateVARCHAR(100)

DECLARE@selecteditemVARCHAR(100)

SELECT@toUpdate = 'var2_N',

@selecteditem = 'var1_N'

SELECT@strSQL = ' UPDATE dbo.tbl_data '

+ ' SET var2 = ' + @toUpdate

+ ' WHERE var1 = ' + @selecteditem

EXECUTE ( @strSQL )

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

simplykash Valued Member Points: 70 More actions · Answer 2

actually i have to pass the column name as a parameter

i have a table with various column names and i want to give control

to user to select the columns to update.

the user will select two columns dynamically,something like this

(@ColumnSelected nvarchar(max),

@ColumnToUpdate nvarchar(max),

as

begin

update tbl_data

set @ColumnToUpdate = xxx

where @ColumnSelected = yyy

vaibhav.tiwari SSCrazy Points: 2026 More actions · Answer 3

You can use like this

Even You can pass the value xxx and yyy as a parameter

to make it more generalize.

DECLARE @SQL AS VARCHAR(MAX)

DECLARE @ColumnToUpdate nvarchar(max)

DECLARE@ColumnSelected nvarchar(max)

SELECT@ColumnToUpdate = 'Var1',

@ColumnSelected = 'Var2'

SET@SQL= ' Update tbl_data'

+' SET ' + @ColumnToUpdate + ' = xxx'

+' WHERE ' + @ColumnSelected + '= yyy'

EXECUTE ( @SQL )

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Lowell SSC Guru Points: 323546 More actions · Answer 4

basically my recommendation mirrors vaibhav's below; four parameters, two for column names and two for values;

if you are going to do that, however, why not just build the whole , complete update statement on the client app(which already has determined the table and column names, along with the new values)

it's probably easier and cleaner to build the whole string and just .ExecuteNonQuery instead of an adapter with a parameter object and four parameters; also makes it easier in situations where you are changing TWO columns at the same time;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Paul White SSC Guru Points: 150468 More actions · Answer 5

My recommendation would be to redesign the table to use a more relational form.

http://databases.about.com/od/specificproducts/a/firstnormalform.htm

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

I agree with Paul on this...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)