August 11, 2005 at 3:30 pm
when you have an error using dynamic sql try ouputting string to text so you can see the query that is being run
declare @sql varchar(1000), @x varchar(10)
set @x = 'field'
set @sql = 'update table set column = ' + @x
print @sql
-- exec(@sql)
Results
update table set column = field
then execute that in query analyzer and you'll get the error
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'field'.
your build query string should actually be this.
set @sql = 'update table set column = ''' + @x + ''''
which makes your update look like this
update table set column = 'field'
You need to read the following prior to embarking on a system that uses alot of dynamic sql
http://www.sommarskog.se/dynamic_sql.html
August 11, 2005 at 3:30 pm
bless you! the simplest syntax can be the hardest sometimes.
many thanks
ryan
August 11, 2005 at 5:19 pm
I'm gonna go on a limb here and assume that there's more to the script. Can we know why you think you need dynamic sql here?
Cause if that's all there is to it, then you just need to do :
update table set column = @variable
August 12, 2005 at 6:56 am
yes, much more script. and the ''' worked fine. i was using the print function, but i must have had a brain cramp. i never tried doubling up the quotes.
August 12, 2005 at 7:03 am
There's still the question of why you think you need dynamic sql here...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply