Resultset as output-parameter

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Klaus (anonymous_at_discussions.microsoft.com)
Date: 06/03/04 

Date: Thu, 3 Jun 2004 06:50:02 -0700

Hi

Just in case anyone else needs to return a query-
resultset from a sp as an output-parameter.

A work-around could be something like this:

CREATE TABLE test (c1 int, c2 varchar(15))
go 

--
INSERT INTO test
VALUES (1, 'test1')
go
INSERT INTO test
VALUES (2, 'test2')
--
CREATE PROCEDURE sp_test @cursor CURSOR VARYING OUTPUT
AS
SET @cursor = CURSOR FOR SELECT * FROM test
OPEN @cursor
go
--
DECLARE @cursor CURSOR, @c1 INT, @c2 VARCHAR(15)
CREATE TABLE #temp_test (c1 int, c2 varchar(15))
EXEC sp_test @cursor OUTPUT
FETCH NEXT FROM @cursor INTO @c1, @c2
WHILE @@fetch_status = 0
BEGIN	
	INSERT INTO #temp_test VALUES (@c1, @c2)
	FETCH NEXT FROM @cursor INTO @c1, @c2
END
CLOSE @cursor
--
SELECT * FROM #temp_test
--
DROP PROCEDURE sp_test
go
DROP TABLE #temp_test
go
DROP TABLE test
go
//Klaus
>-----Original Message-----
>Hi all
>
>Is it possible to create a stored procedure, that 
returns 
>a resultset (from a select contained in the sp) as a 
>output parameter.
>
>If yes, could anyone please provide me with a simple 
>example.
>
>TIA
>Klaus
>.
>

Relevant Pages

  • Re: SelectMethod=cursor and SelectMethod=direct
    ... All the database updates/inserts are done by sql using a prepared statement and then setting the field parameters. ... My only concern is the memory usage as a direct cursor reads the complete resultset into memory. ... If the free MS driver reads everything in immediately, it's just something you would have to deal with, alloting your client enough memory to handle it. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Opinions on approach, please...
    ... To emulate this I plan on using an SQL cursor. ... I COULD do it with a resultset if it wasn't in COBOL or could ... Is it because when you FETCH from a cursor you go back to the ...
    (comp.lang.cobol)
  • Re: while loop in a while loop
    ... Depending on your database vendor and JDBC driver version, ... might not be able to reset the ResultSet cursor. ... The method ResultSet.firstmoves the cursor to the first row in the ...
    (comp.lang.java.programmer)
  • Re: TYPE_SCROLL_SENSITIVE
    ... the type for a ResultSet object that is scrollable and generally ... A sensitive cursor picks up data modifications impacting ... private static void update1throws SQLException { ...
    (comp.lang.java.programmer)
  • Re: Invalid cursor state - Am I completely stupid?
    ... > Therefore the resultset is on the first row ... Okay, suppose I did move the cursor one row forward. ... I suspect that my resultSet is empty, ...
    (comp.lang.java.programmer)