April 20, 2007 at 8:00 am
I would like to find any information specific to using SP in SSRS. A few basic questions I have are:1) How can you access a Return Code? 2) How do you access an Output Parameter? 3) When using #temp tables how do you access input parameters? 4) How can I access @@xxx values such as @@Error?
Thank you,
Herb
April 20, 2007 at 8:52 am
Basically......
Well, You don't.
Reporting services only works with recordsets.
No access to Return Codes.
I have never needed to use Output parameters, but I don't think you can as mentioned RS only works with recordsets.
I'm not sure what you mean by Question 3.
Perhaps this helps a little
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=150&messageid=359680#bm359702
@@Error should be handled in your procedure to return a recordset.
So set the error code, and return in the select statement.
or use @@Error to properly return your recordset.
April 24, 2007 at 2:49 pm
Ray, I don't think that's accurate.
If you want to get a return value from a sproc, try this in the query window:
EXEC @ReturnValue = StoredProcedureName
SELECT @ReturnValue
You will need to create a report parameter, don't prompt the user; set the datatype to INT and give it a default value. The report parameter will have your result once you run it.
You can do something similar to this to get a value from an output parameter.
To get data from the @@ variables, just assign to a regular variable and return in the recordset, or output parameter, or return val if it's an INT.
You should pick up the excellent book "Hitchhiker's Guide to SQL Server 2000 Reporting Services" for more info. I highly recommend it.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply