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.
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.