September 10, 2010 at 7:34 am
Hello, i have an existing stored procedure that I originally wrote for Oracle and I now have ported it over to SQL Server 2008. Here is the definition of the proc:
CREATE PROCEDURE SP_ORDER_PURGE @pArch INT = 90, @pPurge INT = 180, @pStats VARCHAR
When I call the stored procedure I need to pass an in clause for the pStats parameter like so.
exec SP_ORDER_PURGE 90, 180, '(''Shipped''' + ',' + '''Partially Shipped''' + ',' + '''Canceled''' + ',' + '''Terminated'')'
This works fine in Oracle with the + concatenation replaced with the ||. It does not seem to work in SQl server as it does not like the +. Here is the error I get when calling it.
Msg 102, Level 15, State 1, Server L303639, Line 1
Incorrect syntax near '+'.
What adjustments would I need to make in order for this to work correctly?
Thank you!
Best Regards,
~David
September 10, 2010 at 7:40 am
Try placing your values into a varchar variable and execute the sp by passing the variable
Example
DECLARE @strSQL varchar(100)
set @strSQL = '(''Shipped''' + ',' + '''Partially Shipped''' + ',' + '''Canceled''' + ',' + '''Terminated'')'
exec SP_ORDER_PURGE 90, 180, @strSQL
September 10, 2010 at 8:13 am
Yes, that works! Thank you for your input!
🙂
Best Regards,
~David
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply