Re: How to define cursor on temp table?

From: Aaron [SQL Server MVP] (ten.xoc_at_dnartreb.noraa)
Date: 08/24/04 

Date: Tue, 24 Aug 2004 11:40:50 -0400

Maybe you need to show your code, because this works fine for me:

CREATE PROCEDURE dbo.blat
AS
BEGIN
 SET NOCOUNT ON

 SELECT a=1,b=2 INTO #tmp

 DECLARE @a TINYINT, @b TINYINT

 DECLARE foo CURSOR FOR
  SELECT a,b FROM #tmp

 OPEN foo

 FETCH NEXT FROM foo INTO @a, @b
 WHILE (@@FETCH_STATUS=0)
 BEGIN
  PRINT @a
  PRINT @b
  FETCH NEXT FROM foo INTO @a, @b
 END
 CLOSE foo
 DEALLOCATE foo

 DROP TABLE #tmp
END
GO

EXEC dbo.blat
GO

DROP PROC dbo.blat
GO 

-- 
http://www.aspfaq.com/
(Reverse address to reply.)
"Snake" <Snake@discussions.microsoft.com> wrote in message
news:80B78D02-6257-4B37-82ED-DC456E592826@microsoft.com...
> I have a procedure that defines a cursor on a temp table (#temp1) but at
> run-time the following message is displayed " Invalid object name #temp1 "
on
> the Declare statement for the cursor.  This implies that one can not open
a
> cursor on a local temp table created in the same procedure.
>
> So now I need an alternate method of simulating a cursor in my While-loop
in
> my stored proc. Any suggestions?
>
> Thanks,
> Michael

Relevant Pages

  • Re: Aggregation with text
    ... declare @b varchar ... DECLARE cur CURSOR FOR ... SELECT distinct a from foo ... DECLARE cur1 CURSOR FOR ...
    (microsoft.public.sqlserver.programming)
  • Silent performance killer with promoted column
    ... CREATE TABLE FOO( ... INSERT @tmp VALUES ... SET NOCOUNT ON SET STATISTICS IO ON ...
    (microsoft.public.sqlserver.xml)
  • Re: How to implement sizeof operator
    ... project i want to implemnent my own sizeof operator function (like ... Hint: macros can declare variables. ... difference between foo and foo, where the first foo was declared using ... No, at compile-time, it can be done in pure ISO C. ...
    (comp.lang.c)
  • Re: Subroutines with &
    ... print foo(); ... In addition to the expected "Howdy", it produces the following warning: ... You could declare the sub above the ... Or, you could forward declare the sub, like this: ...
    (comp.lang.perl.misc)
  • Re: Calling functions declared in an entity
    ... In VHDL it is possible to declare the following entity: ... entity Foo is ... ARCHITECTURE rtl OF ent_item_decl IS ...
    (comp.lang.vhdl)