Stored Procedures, Recordsets, and Return Values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedures, Recordsets, and Return Values

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-05 : 09:49:16

Gary writes "I hope this isn't a stupid question but I've searched everywhere for the answer with no success. It's something I'd assumed would be a frequent request. I'd like to be able to return a record set from a stored procedure along with a return value. While I know this can be done however I'd like to access the return value BEFORE processing the record set. This way I'd be able to know ahead of time whether the record set should be processed.

CREATE PROCEDURE sp_getsalesdata(@SellerID INT) AS
BEGIN
SET NOCOUNT ON

DECLARE @iError INT
DECLARE @iRemoteSystemID INT

SELECT *
FROM SalesData
WHERE SalesData.SellerID = @SellerID

SET @iError = @@ERROR

IF @iError <> 0
BEGIN
RETURN -1
END
ELSE
BEGIN
RETURN 0
END
END

I understand that the Return code can be accessed AFTER the recordset has been processed but it would be nice to examine the return value before processing the recordset.

Thanks,

Gary"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-05 : 12:33:51

If you use a client side sursor (means retrieving all the data to the client) then you can disconnect the recordset and the return code is available before the app processes the recordset.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources