using cursor to call another proc

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 2008 Forums

Transact-SQL (2008)

using cursor to call another proc

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2014-12-04 : 12:23:17

I am trying to create a cursor to go through records and then execute another stored proc as it finds the records. My script works, but only returns one record and then ends. I am sure I have something in my code out of order.

begin

DECLARE @ID varchar(10),
@CompanyID varchar(10),
@Is_Admin bit

declare name_Cursor cursor
for
select top 1 ID, co_id,CO_count
from Participants
where id is not null
and CO_count in ('1','0')
and ID in (SELECT top 1 ID from subs WHERE member_type in ('CC'))

open name_Cursor
fetch next from name_Cursor
into @ID, @CompanyID, @Is_Admin

EXEC ap_MatchBack @ID, @CompanyID

close Name_Cursor

deallocate Name_Cursor

end

Thanks!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-04 : 12:31:45

Lookup cursors here: http://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/

You need to write a WHILE loop as in the example.

dzabor
Posting Yak Master

138 Posts

Posted - 2014-12-04 : 13:31:05

Thanks

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-12-04 : 13:43:40

The "top 1" in the main SELECT is limiting your results to one row. You need to get rid of that to retrieve multiple rows, and then set up a FETCH loop as with any cursor:

select ~~top 1~~ ID, co_id,CO_count
from Participants
where id is not null
and CO_count in ('1','0')
and ID in (SELECT top 1 ID from subs WHERE member_type in ('CC'))

dzabor
Posting Yak Master

138 Posts

Posted - 2014-12-08 : 10:58:14

I will try that. Thx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources