Hello,

I'm having trouble using output procedures with SQL. I've started by creating a stored procedure with multiple output parameters:

ALTER PROCEDURE [dbo].[MobileActivity15]
@RequestTime datetime = null OUT
,@Activity int = null OUT
,@TransactionLists int = null OUT
,@AcctList int = null OUT
,@Transfers int = null OUT
,@Payments int = null OUT
AS
BEGIN
SET NOCOUNT ON;


select @RequestTime = GetDate()
,@Activity = coalesce(count(*),0)
,@TransactionLists = coalesce(sum(case when tblLog.LogNote like 'Transactions' then 1 else 0 end),0)
,@AcctList = coalesce(sum(case when tblLog.LogNote like 'Acct List' then 1 else 0 end),0)
,@Transfers = coalesce(sum(case when tblLog.LogNote like '%xfer' then 1 else 0 end),0)
,@Payments = coalesce(sum(case when tblLog.LogNote like '%pmt' then 1 else 0 end),0)
from ServerName.dbo.Logdb as m3Log
where m3Log.LogDate > DateAdd(minute,-15,GetDate())

select @RequestTime as RequestTime
,@Activity as Activity
,@TransactionLists as TransactionLists
,@AcctList as AcctList
,@Transfers as Transfers
,@Payments as Payments

return
END

Then, I setup a SQL task in VisualCron:


When I run the job, I get an error stating Exception in Task and no Output and no Output (Error) messages. What am I doing wrong?

Hi Frank,

I think you should remove the OUT in the parameters in the Stored Procedure.
Then I see ServerName.dbo in your query, it might be something you obfuscated, or else name it to the right servername.

If you use the OUT prefix, and you use the select all parameters, there are two sets of results from this SP. I think VC can handle only one resultset.

Regards
Erik

---

Hi

You know you can just do a select and get the output that way?

CREATE PROCEDURE [dbo].[MobileActivity15]
AS
BEGIN
SET NOCOUNT ON;

SELECT GetDate() as RequestTime,
COALESCE(count(*),0) AS Activity,
COALESCE(SUM(case when tblLog.LogNote like 'Transactions' then 1 else 0 end),0) as TransactionLists,
COALESCE(SUM(case when tblLog.LogNote like 'Acct List' then 1 else 0 end),0) as AcctList,
COALESCE(SUM(case when tblLog.LogNote like '%xfer' then 1 else 0 end),0) as Transfers,
COALESCE(SUM(case when tblLog.LogNote like '%pmt' then 1 else 0 end),0) as Payments
from ServerName.dbo.Logdb as m3Log
where m3Log.LogDate > DateAdd(minute,-15,GetDate());

END

Hi Thomas,

You did a better version so it looks. I's not necessary to use input variables if you do not need them.
I did not look to the query itself.

Regards
Erik

---