Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Unanswered: Declaring a cursor on a temporary table

    How do I declare a cursor on a table like #TempPerson
    when thhs table is only created when I do :

    Select Name, Age Into #TempPerson From Person

  2. #2
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    can i ask especially what your trying to do more in detail please.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I hjave NEVER done this...and only did this for myself to see if it works (didn't see why it wouldn't), but I highly DO NOT recommed this...

    Code:
    USE Northwind
    GO
    SELECT * INTO #TEMP FROM Orders
    
    DECLARE
      @OrderID		int
    , @CustomerID		nchar(10)
    , @EmployeeID		int
    , @OrderDate		datetime
    , @RequiredDate		datetime
    , @ShippedDate		datetime
    , @ShipVia		int
    , @Freight		money
    , @ShipName		nvarchar(80)
    , @ShipAddress		nvarchar(120)
    , @ShipCity		nvarchar(30)
    , @ShipRegion		nvarchar(30)
    , @ShipPostalCode	nvarchar(20)
    , @ShipCountry		nvarchar(30)
    , @LoopCounter		int
    
    
    DECLARE myCursor99 CURSOR
    FOR
    SELECT 	  OrderID
    	, CustomerID
    	, EmployeeID
    	, OrderDate
    	, RequiredDate
    	, ShippedDate
    	, ShipVia
    	, Freight
    	, ShipName
    	, ShipAddress
    	, ShipCity
    	, ShipRegion
    	, ShipPostalCode
    	, ShipCountry
    FROM #Temp
    
    OPEN myCursor99
    
    SELECT @LoopCounter = 0
    
    FETCH NEXT FROM myCursor99 INTO
    	  @OrderID
    	, @CustomerID
    	, @EmployeeID
    	, @OrderDate
    	, @RequiredDate
    	, @ShippedDate
    	, @ShipVia
    	, @Freight
    	, @ShipName
    	, @ShipAddress
    	, @ShipCity
    	, @ShipRegion
    	, @ShipPostalCode
    	, @ShipCountry
    
    WHILE @@FETCH_STATUS = 0
      BEGIN
    	-- Some Code
    	SELECT @LoopCounter = @LoopCounter + 1
    	FETCH NEXT FROM myCursor99 INTO
    	  @OrderID
    	, @CustomerID
    	, @EmployeeID
    	, @OrderDate
    	, @RequiredDate
    	, @ShippedDate
    	, @ShipVia
    	, @Freight
    	, @ShipName
    	, @ShipAddress
    	, @ShipCity
    	, @ShipRegion
    	, @ShipPostalCode
    	, @ShipCountry
      END
    CLOSE myCursor99
    DEALLOCATE myCursor99
    DROP TABLE #Temp
    SELECT 'Loops incurred: ' + CONVERT(varchar(15),@LoopCounter)
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    I've simplified a stored proc
    It's the "declare Age dynamic scroll cursor for select Age from #TempPerson
    " that doesn't work


    create procedure GetAges
    as
    begin
    declare @Age char(20),
    declare @PreviousAge char(20),

    declare Age dynamic scroll cursor for select Age from #TempPerson

    select Name,Age into #TempPerson From Person
    insert into #TempPerson (Name,Age) Select LastName,Age From OtherPerson Where TypePerson = '1'

    select @PreviousAge=' '

    open Age
    while 1=1
    begin
    fetch next Age into @Age
    if @Age <> @PreviousAge
    ...
    select @Age=@PreviousAge
    close Age
    end

  5. #5
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    OK Brett thank you --- AGAIN ---
    that principle will work
    I'll give you some news tomorrow
    I'm fed up with stored proc for today !!!!!

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Good Luck, but I bet you, if you tell me what you're trying to do, we can find a set based solution....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    I'm migrating the Sybase structure to SQL Server
    and I've go to rewrite
    Very-Stupidly-And-Badly-Written-Sybase Watcom-Stored proc

    I hate working with programs written by spagetti-minded-programmers


    So you don't want to see to stored proc (REALLY don't !!!)

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yup, sometimes it's not an option....

    I will go feel bad for you now.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •