Please help- cursor loop returning nothing

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)

Please help- cursor loop returning nothing

Author

Topic

jrb2971
Starting Member

5 Posts

Posted - 2002-04-29 : 11:43:23

this is my stored procedure:
[size2]
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER PROCEDURE spAllPlayListsNew AS

DECLARE @ppl nvarchar(50)

DECLARE c1 CURSOR READ_ONLY FOR
SELECT distinct Staff
FROM Playlists

OPEN c1

FETCH NEXT FROM c1
INTO @ppl

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @ppl

FETCH NEXT FROM c1
INTO @ppl
END

CLOSE c1
DEALLOCATE c1

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
[/size2]

The sql statement returns 3 records, so I know the select statment works. I'm using Microsoft SQL Server 2000 - 8.00.194 (Intel X86)

I am using sample code right from tis site tutorial.. anyone have any idea what is going wrong? why this stored procedure is returning nothing while the source select statement does return 3 values?

thanks.. i'm banging my head against the wall on this one.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-29 : 11:50:05

The PRINT statement does not return a recordset/resultset/rowset (whichever term you prefer), it simply outputs a value. Since it is not a query result by any means, it doesn't return rows as a SELECT statement would.

There is no need for you to use cursors, certainly not in this example. If your code is different from this, please post what you're using now and what you need to accomplish, and we can get you a set-based solution that doesn't use cursors.

jrb2971
Starting Member

5 Posts

Posted - 2002-04-29 : 12:14:25

Sorry for not explainting it more. I'm am trying to do a top 10 albums for each staff member on my music web page. In my research I have seen that a cursor in a loop can loop thru the list of staff members using that select distinct query and run a stored procedure for each staff name:

SELECT TOP 10 Artist, Album, [Date]
FROM dbo.Playlists
WHERE (Staff = @name)
ORDER BY [Date] DESC

so my results would be:
StaffName1
album5
album2

StaffName2
album4
album8

Right now, I'm using asp to loop through a recordset and then pass a stored procedure the staffnames - but I would move this processing to SQL. Thanks for your help

jrb2971
Starting Member

5 Posts

Posted - 2002-04-29 : 12:15:31

also I was using the print statment for debuggging in the query analyzer. the print statement didn't even return values for debuggging.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-29 : 12:35:00

Did you post this originally?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=15349

What's wrong with the solution provided?

jrb2971
Starting Member

5 Posts

Posted - 2002-04-29 : 13:11:23

sorry - i just read that - i think my fever i had this weekend wiped out my memory - i'll go try that out now