February 11, 2010 at 9:31 am
Can anyone tell me where my syntax is wrong in this statement:
Set@sqlP ='
UpdateIARTS..PWDPermitConversionData
SetConversionStatusPrev' + Cast(@PreviousCounter as varchar) + ' = ' + Cast(@Error as varchar) + ',
ConversionErrorMessagePrev' + Cast(@PreviousCounter as varchar) + ' = ''' + Cast(@ErrorMessage as varchar) + '''
WherePWDPermitConversionDataID = ' + Cast(@ID as varchar) + '
'
It breaks on the line that sets the 'ConversionErrorMessagePrev' part.
When I output (aka Print @sqlP), it returns 1 space.
BTW - Error and ErrorMessage are the values returned from a try/catch block. In the catch block, I am doing:
Set@Error = Error_Number()
Set@ErrorMessage = Error_Message()
Set@ErrorMessage = Replace(@ErrorMessage, '''', '''''')
TIA
February 11, 2010 at 9:47 am
Also, when I output the @ErrorMessage variable, it comes out as a space also.
February 11, 2010 at 10:01 am
Some of your variables are set to null along the way. Try to catch it with ISNULL.
BTW, your code is prone to sql injection, I suggest that you revise it a bit:
Set @sqlP = '
Update IARTS..PWDPermitConversionData
Set [ConversionStatusPrev' + Cast(@PreviousCounter as varchar) + '] = @err,
[ConversionErrorMessagePrev' + Cast(@PreviousCounter as varchar) + '] = @msg
Where PWDPermitConversionDataID = @id '
EXEC sp_executesql @sqlP, N'@err int, @msg varchar(1000), @id int', @error, @errorMessage, @id
Hope this helps
Gianluca
-- Gianluca Sartori
February 11, 2010 at 10:04 am
I got it. If @ErrorMessage was null, it would never build the dynamic sql...it would just set it to null also.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply