Stored Procedure output

[NeilV]

Hello,

I am trying to create a Stored procedure in the following way:
CREATE PROC RetroReport
@RetroCreated int output,
@RetroShipped int output,
@RetroCancelled int output,
@Retro2DayGap int output
AS
SELECT @RetroCreated =(Select count(*) FROM Retrofit_Orders where date_created >= DATEADD(day, -7,getdate()))
SELECT @RetroShipped =(SELECT count(*) FROM Retrofit_Orders where deliverynote_printed >= DATEADD(day, -7,getdate()))
SELECT @RetroCancelled = (SELECT count(*) FROM Retrofit_Orders where date_voided >= DATEADD(day, -7,getdate()))
SELECT @Retro2DayGap = (SELECT count(*) FROM Retrofit_orders where Datediff(day,date_created,deliverynote_printed) > 2 or Datediff(day,date_created,date_voided) > 2)
GO

I was hoping that it would return 4 values (1 for each query). However, when i try to run it I get an error saying
Procedure 'RetroReport' expects parameter '@RetroCreated', which was not supplied.
This doesn't make sense as I have specified that all the parameters as output values.
What am i doing wrong?

Neil

 

[JohnDTampaBay]

I believe all parameters are treated as INPUT parameters and can optionally be set as OUTPUT parameters also. Give them DEFAULT values so SQL won't complain when they aren't passed in.

CREATE PROC RetroReport
@RetroCreated int = NULL output,
@RetroShipped int = NULL output,
@RetroCancelled int = NULL output,
@Retro2DayGap int = NULL output
AS
SELECT @RetroCreated =(Select count(*) FROM Retrofit_Orders where date_created >= DATEADD(day, -7,getdate()))
SELECT @RetroShipped =(SELECT count(*) FROM Retrofit_Orders where deliverynote_printed >= DATEADD(day, -7,getdate()))
SELECT @RetroCancelled = (SELECT count(*) FROM Retrofit_Orders where date_voided >= DATEADD(day, -7,getdate()))
SELECT @Retro2DayGap = (SELECT count(*) FROM Retrofit_orders where Datediff(day,date_created,deliverynote_printed) > 2 or Datediff(day,date_created,date_voided) > 2)
GO


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[NeilV]

Thanks for that! It now lets me execute the proc but I don't actually get any output, but query analyzer tells me the command has executed successfully.

Neil

 

[mwolf00]

declare @val1 int, @val2 int, @val3 int, @val4 int

exec RetroReport @val1, @val2, @val3, @val4

print @val1

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[NeilV]

Thanks for that, it does the trick!

Neil