Using exec('select...') in an update statement

[tokuzumi]

What is the proper syntax for using the exec('sql statement') inside of an update statement?

I have an update statement like this:

update #tmp_tbl set
field1 = (exec('select ' + (select fieldname from fieldnametable where category = id) + ' from datatable where trans_id = transaction_id'))

fieldname is a varchar field in fieldnametable.

I know I can run an exec('sql statement') by itself, but as soon as I try to build a string in my exec statement, I get an error about incorrect syntax near exec, and incorrect syntax near +, etc. Thanks for your help in advance.

 

[DrSql]

You have to follow some what belwo logic to make your syntax correct.

decalare @sqlstmnt char(255)
select @sqlstmnt='(select fieldname from fieldnametable where category = id) + ' from datatable where trans_id = transaction_id'))
exec @sqlstmnt

Dr.Sql
Good Luck.

 

[tokuzumi]

Dr. Sql,

Thanks for the reply. However, there are only 3 single quotes in your reply. I would get a syntax error.

 

[DrSql]

Try this

declare @sqlstmnt char(255)
select @sqlstmnt=
'select (select fieldname from fieldnametable where category = id)
from datatable where trans_id = transaction_id'
print @sqlstmnt
exec @sqlstmnt

Dr.Sql
Good Luck.