declare curW cursor for EXEC spMySproc - Trying to assign sp resultset to cursor variable 
Author Message
 declare curW cursor for EXEC spMySproc - Trying to assign sp resultset to cursor variable

In a stored procedure I am getting an error trying to assign a resultset from a stored procedure into a cursor variable.

Here is the line of code in particular..

declare curW cursor for
 EXEC sp_Crosstab




It seems to be flaking out with the EXEC statement.

Anyone know of a way around this ?

Thanks,

Dan



Tue, 14 Feb 2006 14:36:20 GMT
 declare curW cursor for EXEC spMySproc - Trying to assign sp resultset to cursor variable

You might be better off re-writing your procedure as a table-valued
function.

Or perhaps you could define your requirements so someone can show you how to
accomplish your desired results *without* using a cursor (they are very
seldom a true requirement, but are often used because people are stuck in
this "process one row at a time" mindset).

If your procedure is named correctly, and you are really trying to do a
crosstab/pivot, perhaps some of the following links will help you:

Code:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q175574
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_14_04j7.asp
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608
http://www.sqlteam.com/item.asp?ItemID=2955

Software:

http://www.rac4sql.net
http://www.ag-software.com/xp_ags_crosstab.asp
http://www.tysonsoftware.co.uk/TheQueryTool.asp


In a stored procedure I am getting an error trying to assign a resultset
from a stored procedure into a cursor variable.

Here is the line of code in particular..

declare curW cursor for
 EXEC sp_Crosstab




It seems to be flaking out with the EXEC statement.

Anyone know of a way around this ?

Thanks,

Dan



Tue, 14 Feb 2006 14:45:04 GMT
 declare curW cursor for EXEC spMySproc - Trying to assign sp resultset to cursor variable

if you want to loop through resultset returned by  your stored procedure you will have to transfer output of stored procedure to
temporary table and declare a cursor on it.
Ex:

create table #t
(spid smallint,
ecid smallint,
status nchar(50),
loginame nvarchar(50),
hostname nchar(50),
blk char(50),
dbname nvarchar(50),
cmd nchar(50))

insert into #t exec sp_who

declare c1 cursor local for
select * from #t

--
-Vishal


  In a stored procedure I am getting an error trying to assign a resultset from a stored procedure into a cursor variable.

  Here is the line of code in particular..

  declare curW cursor for
   EXEC sp_Crosstab




  It seems to be flaking out with the EXEC statement.

  Anyone know of a way around this ?

  Thanks,

  Dan



Tue, 14 Feb 2006 14:50:32 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. How to assign a variable to cursor variable.

2. Declare a cursor using EXEC

3. declare exec("...") as cursor

4. How declare cursor using EXEC

5. Scope of EXEC SQL DECLARE <cursor>

6. Core dump in EXEC SQL DECLARE xx_cur CURSOR FOR xid

7. Cursor was not declared error on rdo resultset

8. Could not complete cursor operation, because the table schema changed after cursor was declared

9. Could not complete cursor operation, because the table schema changed after cursor was declared

10. Why declare cursor and open cursor both cost same amount of time

11. HELP: Trying to declare cursor from a vairable source

12. How to use existing SP in DECLARE Cursor stmt


 
Powered by phpBB® Forum Software