Using Cursors to find data - 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 2005 Forums

Transact-SQL (2005)

Using Cursors to find data

Author

Topic

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-05-30 : 14:58:00

What I would like to do, is loop through a table, extract two fields from each record, and pass the values of those fields to a stored proc. I found some help on using cursors to do this, but can I use two cursors at the same time? Below is what I would like to do:

DECLARE @cursorPhone CURSOR, @cursorName cursor, @phone VARCHAR(30), @UserName varchar(50)

SET @cursorPhone = CURSOR
FOR
SELECT userPhone FROM dbo.RPM_Data

OPEN @cursorPhone

FETCH NEXT FROM @cursorPhone INTO @Phone

SET @cursorName = CURSOR
FOR
SELECT userName FROM dbo.RPM_Data

OPEN @cursorName

FETCH NEXT FROM @cursorName INTO @UserName

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_Subscription_Individual @phone, @UserName

FETCH NEXT FROM @cursorPhone INTO @phone
FETCH NEXT FROM @cursorName INTO @Username
END

DEALLOCATE @cursorPhone
DEALLOCATE @cursorName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-30 : 15:08:06

better to do it using a WHILE loop.May be like:-

DECLARE @PK int,@phone VARCHAR(30), @UserName varchar(50)

SELECT @PK=MIN(PKCol)
FROM dbo.RPM_Data

WHILE @PK IS NOT NULL
BEGIN
SELECT @phone =userPhone,@UserName= userName 
FROM dbo.RPM_Data
WHERE PKCol=@PK
EXEC sp_Subscription_Individual @phone, @UserName

SELECT @PK=MIN(PKCol)
FROM dbo.RPM_Data
WHERE PK > @PK
END

b/w what's the SP doing?

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-05-30 : 15:14:20

Thanks a lot! I thought loops were NOT the best way to get things done, thats why I tried a cursor! The sp is running a report on SSRS, and I need to pass the parameters.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources