ref cursors in T-sql

[dataforums]

Hi All,
I have a requirement that the STORED PROCEDURE should return
records same like storedprocedure in sqlserver. In oracle ref cursors are used to return multiple values, is there anything in t-sql that can perform this.

 

[hmckillop]

I have a requirement that the STORED PROCEDURE should return
records same like storedprocedure in sqlserver.

Not quite clear what this means, but at a guess you either want to return multiple recordsets, or multiple return parameters.
In the first case a series of SELECT statements in the stored procedure will return multiple resultsets.

CREATE Proc testProc1
AS

SELECT * from Table1
SELECT * FROM Table2
--...etc

In the latter case declare multiple output variables

CREATE PROC testProc2
( @po_OutputParameter1 VARCHAR(10),
@po_OutputParameter2 VARCHAR(10),
@po_OutputParameter3 VARCHAR(10),
@po_OutputParameter4 VARCHAR(10)
AS

SELECT @po_OutputParameter1 = field1, @po_OutputParameter2 = field2 from MyTable1

SELECT @po_OutputParameter3 = field1, @po_OutputParameter4 = field2 from MyTable2

"I'm living so far beyond my income that we may almost be said to be living apart