cursor + temporary tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

cursor + temporary tables

Author

Topic

monkey
Starting Member

3 Posts

Posted - 2006-07-04 : 00:23:59

Hi,
I have a task that needs to port sql scripts from ORACLE to SQL SERVER, now I encountered a problem I am not sure which is the best way to solve...
In the original ORACLE file, there is a type record defined in the package,


TYPE typeA IS RECORD (
  field1 int
  field2 int
  field3 int
)

and there are quite a few procedures that need to utilise this type, eg a procedure declares a variable of type typeA and gives it values, then this procedure passes on this variable to other procedures for them to do work (insert/update table etc) based on this variable (In a more abstract way, it is like fetching a row in a table and copy the whole row's value into a variable and pass this variable to other procedures for processing). The caller procedure looks something like this:


PROCEDURE caller IS
  CURSOR cursor1 IS
    SELECT field1, field2, field3
    FROM TABLE1;

  var1 typeA;

  BEGIN
    OPEN cursor1;
    FOR rec IN cursor1 LOOP
      BEGIN
        var1.field1 := rec.field1;
        var1.field2 := rec.field2;
        var1.field3 := rec.field3;

        calledProc1(var1);
        calledProc2(var1);
      END;
    END LOOP;

As sql server doesn't have an equivalent structure to "TYPE .. RECORD" , and I don't want to pass a whole lot of variables to each called procedure, and I really need to use procedures to do this (as opposed to functions), so my only choice I think is to use a temporary table?


CREATE PROCEDURE caller AS BEGIN
  DECLARE @field1 int
  DECLARE @field2 int  
  DECLARE @field3 int

  DECLARE cursor1 CURSOR LOCAL FOR 
    SELECT field1, field2, field3
    FROM TABLE1
	
  CREATE TABLE #tempTable (field1, field2, field3)

  OPEN cursor1
  FETCH NEXT FROM cursor1 INTO @field1, @field2, @field3
  WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      INSERT INTO #tempTable VALUES (@field1, @field2, @field3)
      EXEC calledProc1 --called procedure will use this temp table
      FETCH NEXT FROM cursor1 INTO @field1, @field2, @field3 
    END
  CLOSE cursor1 
  DEALLOCATE cursor1 
END

This approach seems quite cumbersome, and I have to declare all the variables just for cursors to fetch into (since I have about 20 fields instead of just 3 in the above example)... is there another way of doing it? or perhaps a simplified version?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-04 : 00:45:52

Does the stored procedure calledProc1 & Proc2 has to process record by record ? Can you modify to handle sets ?

If this is possible, then you can


create table #tempTable ( . . . )
insert into #tempTable
select . . . 
from   TABLE1

exec calledProc1 -- called procedure will use the #tempTable and process the records in the temp table

This way, it is more efficient also as you are dealing with sets of data rather than record by record.

Kristen
Test

22859 Posts

Posted - 2006-07-04 : 00:53:35

"I have a task that needs to port sql scripts from ORACLE to SQL SERVER"

My understanding is that Oracle is efficient with cursors.

I can tell you that SQL Server is not, and that performance will post probably be rubbish!

So you may want to consider recoding anything using Oracle cursors to be set-based instead - rather than just porting the code. That might require quite a bit of a re-think I'm afraid ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources