Cursor problems with sp's - 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

Transact-SQL (2000)

Cursor problems with sp's

Author

Topic

sda
Starting Member

3 Posts

Posted - 2004-04-20 : 06:57:44

Can anyone help with this?

I need to declare a cursor to loop around results from another sp. for example (simplified)

Proc1 -> Select 'data1'
Select 'data2'
Select 'data3'

Proc2 -> declare cursor .....
"select from Proc1"
Fetch ....
While ....
....
Fetch....
etc.
Thanks!

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-20 : 07:19:06

Are you sure that you need a cursor for this?

You should try and avoid using cursors.

If you post some example code from the stored proc, example data and desired results then you might get good help here.

Duane.

sda
Starting Member

3 Posts

Posted - 2004-04-20 : 09:03:26

I cant help using cursors, Im writing an Interbase to SQL Server translation utility. One of Interbases strong points is they can say ...

Select * from StoredProc

So I have to try and do this . e.g.

StoredProc1

Declare @Output1 Varchar
Declare @Output2 Varchar

DECLARE a_PATIENT_cursor SCROLL CURSOR FOR
execute StoredProc2 'Parameter'
OPEN a_PATIENT_cursor
FETCH NEXT FROM a_PATIENT_cursor INTO @Output1,@Output2
WHILE @@FETCH_STATUS = 0
begin
-- Do Something with Output1+2
Select @Output1,@Output2
FETCH NEXT FROM a_PATIENT_cursor INTO @Output1,@Output2
end
CLOSE a_PATIENT_cursor
DEALLOCATE a_PATIENT_cursor

StoredProc2

SET NOCOUNT ON
Select 'Value1','Value2'
Select 'Value3','Value4'
Select 'Value5','Value6'
Select 'Value7','Value8'

Obviously the second proc is more complicated than that but its just an example!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-04-20 : 09:14:55

As you might have figured by now, you cannot do this. But you can do this:


CREATE TABLE #tempResults (col1 datatype, col2 datatype...)
INSERT INTO #tempResults 
  EXEC MyStoredProcedure2 @Param1, @Param2

DECLARE @myCursor CURSOR FOR SELECT * FROM #tempResuls
...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-20 : 09:24:49

Well, you can create a temp table with an identity column and put your EXEC in the temp table. Just use a WHILE loop to loop to the maximum column on the temp table.

If you need to nest them, just use two tables. It will be quicker than your cursor.

Make sense?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

sda
Starting Member

3 Posts

Posted - 2004-04-21 : 05:27:12

Thanks for that.

Ive gone down the route of creating a table to hold the results from the sp and then cursor-ing through these results in the other stored proc. Also because these sp's are dynamically created, this caused me the problem of not knowing the parameters, so I had to create a routine which extracted them out of sysobjects.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources