Skip to Content
0
Former Member
Jan 13, 2012 at 12:12 AM

Calling stored procedures with output parameters

523 Views

Hi,

I have a stored procedure which returns a message which I use in an Email. I have tested the procedure within SQL server and it works fine. But when I call it from within a script, I am not getting the message back.

In SQL Server:

declare @m varchar(max)

exec spJobSummary 69, @m output

select @m

output:

-


Job [SAP_ODS_LOAD] completed with a status of "FINISHED" in 86 minutes.

In DS (2 line test script shown):

ADMIN.DBO.SPJOBSUMMARY(69,$message,$rtCode,$rtVal);

smtp_to('leigh[at]test.com.au', 'Results of ' || job_name(), $rtCode || '//' || $rtVal || '~~' || $message || '//', 0,0);

(nb: email edited to comploy with forum rules).

Mail from: </SMTP_TEST> sent at: Fri Jan 13 10:42:30 2012

Message:

=======

ACTA_SP_OK//~~//

So the proc is running ok, but the message

comes back blank.

I can't find an example of using stored procs with output parameters and I suspect the problem is in how I am passing it in my script. If anyone has any suggestions, I'll be very grateful !

Thanks.