dinamic sql variable 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?

 

[philhege]

@result still contains variable markers, which are not evaluated at the exec level. You'll have to concatenate the field names and values literally into the @result variable for this to work.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[victorashi]

first of all, the columns are evaluated by exec, that's why in my table it updates the fields with the string +@nume+,...
and second, i can't concatenate values because i give the table dinamicaly.
that's what i'm asking, can it be done?
i declare the variables and serve them to the procedure, but the exec doesn't see them, why?
is there a way to do it?

 

[philhege]

Once again: The variables are not visible to the EXEC function because it's on a different level from the declaration (read the error msg to see what level number).

Try using sp_executesql and passing the variables to the SP.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.