dinamic sql problem

[victorashi]

i have a problem with dinamic sql, pls help
i want to dinamycally update a table from my database
this is the code i wrote so far:

declare @temp char
set @temp = Cast(@nr_crt as char)
declare @Result varchar (8000)
select column_name into #temp from information_schema.columns where table_name=''+@a+''
select @Result = IsNull(@Result+', ','')+column_name+'=''+@'+column_name+'+''' From #temp where column_name <> 'nr_crt'
print (@Result) --this is for testing
exec ('update '+@a+' set '+@Result+' where nr_crt='+@temp)

@a is the table name
the @result variable looks like this for the table test with columns nr_crt, nume, prenume, meserie:
nume='+@nume+', prenume='+@prenume+', meserie='+@meserie+'

this code works in the way that it puts at a certain nr_crt (for example 1) in a certain table (for example test) the values:

nr_crt nume prenume meserie
1 +@nume+ +@prenume+ +@meserie+


but i want to put the values of the variable @nume, @prenume, @meserie not the string '+@nume+'
i want the exec command to see the variables, like it does with @a and @temp
if i use print instead of exec it returns:

update test set nume='+@nume+', prenume='+@prenume+', meserie='+@meserie+' where nr_crt=1

for @a=test and @nr_crt=1 but the rest of the values i imput doesn't see them as variables
can u help me pls?

 

[jgurgen]

great reference for dynamic sql

http://www.sommarskog.se/dynamic_sql.html