[cfaussie] Re: [OT] SP Cursor

[Daniel Morphett]

At 04:52 PM 2/11/2003 +1000, you wrote:

OK Ok, I wanted to keep it simple, but the complete code is listed below:
[ What I am trying to achieve is; a poll only has one row for each poll, but each poll has many possible answers. The problem comes in when I want to get X number of random polls, I can't just do a join query between the poll and the answer and use TOP X I would not get all the possible answers returned. ]
 
 

How about this? You should end up with the same result.

DECLARE @tempTable TABLE ( pkPollID INT, pollOwnerID INT NULL, pollQuestion VARCHAR(500), pollOwner VARCHAR(12) NULL, dateStamp SMALLDATETIME, pkPollAnswerID INT NULL, pollAnswer VARCHAR(300) NULL, pollValue INT NULL )
DECLARE @pkPollID INT, @pollOwnerID INT, @pollQuestion VARCHAR(500), @pollOwner VARCHAR(12), @dateStamp SMALLDATETIME, @pkPollAnswerID INT, @pollAnswer VARCHAR(300), @pollValue INT

, @topNumber     int    -- the number you wanted to use as "top n"
, @counter       int    -- counter

set @topnumber = 10

DECLARE crsPoll CURSOR
FOR SELECT              --note I removed the top 1, now we get them all (if you know you'll never need more than a certain number, just put it back in                  --top 50 or whatever
   tblPoll.pkPollID,
   tblPoll.fkSomeoneID AS pollOwnerID,
   tblPermissionUser.username AS pollOwner,
   tblPoll.pollQuestion, tblPoll.dateStamp
FROM   tblPoll
LEFT OUTER JOIN tblPermissionUser
ON    tblPoll.fkSomeoneID = tblPermissionUser.fkSomeoneID
WHERE  (tblPoll.startDate <= GETDATE())
AND    (tblPoll.expiryDate IS NULL OR tblPoll.expiryDate >= GETDATE())
AND    (tblPoll.fkStatusID = 1)
ORDER BY  NEWID()
 

OPEN crsPoll
FETCH NEXT FROM crsPoll
INTO @pkPollID, @pollOwnerID, @pollOwner, @pollQuestion, @dateStamp

set @counter = 1

 
WHILE @@FETCH_STATUS = 0 and @counter < @topnumber
BEGIN
 
 INSERT INTO @tempTable (
   pkPollID,
   pollOwnerID,
   pollOwner,
   pollQuestion,
   dateStamp,
   pkPollAnswerID,
   pollAnswer,
   pollValue )
 SELECT @pkPollID,
   @pollOwnerID,
   @pollOwner,
   @pollQuestion,
   @dateStamp,
   pkPollAnswerID,
   pollAnswer,
   pollValue
 FROM  tblPollData
 WHERE (fkPollID = @pkPollID)
 AND   (fkStatusID = 1)
 

@counter = @counter + 1

 FETCH NEXT FROM crsPoll
END
 
CLOSE crsPoll
DEALLOCATE crsPoll
 

----- Original Message -----

From: Daniel Morphett

To: CFAussie Mailing List

Sent: Tuesday, February 11, 2003 4:23 PM

Subject: [cfaussie] Re: [OT] SP Cursor

Don't think the select statement in a cursor can be dynamic.

Couple of questions beg:

if you're selecting top 1 from a table, there is no need for a cursor

if you know the number of rows you want to process (i.e. top 20), you could easily just make a counter, incrementing it with each row

I notice you don't have an order by clause, which I reckon you are going to need for a top n query.

At 02:59 PM 2/11/2003 +1000, you wrote:

I have the following cursor in my Stored Procedure

 

-----------------------------------------------------

DECLARE crsPoll CURSOR

FOR SELECT TOP  1

   column1, column2

FROM   table

-----------------------------------------------------

 

I need the TOP 1 integer to be a variable, i.e. TOP @intTop

 

But this won't work, it says "Incorrect Syntax"

 

I also tried to set the whole select statement as variable and then execute it like so;

 

-----------------------------------------------------

DECLARE crsPoll CURSOR

FOR EXECUTE sp_executesql @sql

-----------------------------------------------------

 

Where the variable @sql is

 

-----------------------------------------------------

DECLARE @sql NVARCHAR(1024)

SET @sql = 'SELECT TOP  ' + CAST(@intTop AS VARCHAR(3)) + '

   column1, column2

FROM   table'

-----------------------------------------------------

 

But still no luck..

Any ideas?

---

You are currently subscribed to cfaussie as: [EMAIL PROTECTED]

To unsubscribe send a blank email to [EMAIL PROTECTED]

MX Downunder AsiaPac DevCon - http://mxdu.com/

Daniel Morphett

Web Developer, DBA

www.daemon.com.au ---

You are currently subscribed to cfaussie as: [EMAIL PROTECTED]

To unsubscribe send a blank email to [EMAIL PROTECTED]

MX Downunder AsiaPac DevCon - http://mxdu.com/

---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]

MX Downunder AsiaPac DevCon - http://mxdu.com/

Daniel Morphett
Web Developer, DBA
www.daemon.com.au ---
You are currently subscribed to cfaussie as: archive@mail-archive.com
To unsubscribe send a blank email to [EMAIL PROTECTED]

MX Downunder AsiaPac DevCon - http://mxdu.com/