Help with @variable statement that needs to end in '

[bmacbmac]

Hi.

I am trying to execute the following statement with a variable:

update lintest..mytable1
set countervalue = (select max (id)+1 from lintest..acres)
where countername = 'acres'

I'm trying to do this with a variable, but since my script ends in a single quote: ' I can't find a correct syntax to make this work. So far I have:

declare @destination varchar(20)
set @destination = 'lintest'

declare @acresupdate varchar(100)
set @acresupdate = 'update ' + @destination + '..mytable1
set countervalue = (select max (id)+1 from ' + @destination + '..acres)
where countername = ' + '''' + 'acres' + ''''
execute (@acresupdate)

I get an 'incorrect syntax' message. I know there has to be an easy fix here but I am stumped.

Thanks!

Brian

 

[jbenson001]

execute your code without the last 'execute" statement and do select @acresupdate
look at what prints out

then look at the length of the variable @acresupdate

increase the length

 

[gk53]

Just increase size of @acresupdate to 400

 

[bmacbmac]

Doh! Thanks so much. Can't believe I didn't catch that.