[cfaussie] Re: [OT] SP Cursor

Taco Fleur Mon, 10 Feb 2003 23:42:51 -0800

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. ]

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

DECLARE crsPoll CURSOR
FOR SELECT TOP  1
   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

WHILE @@FETCH_STATUS = 0
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)

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/

[cfaussie] Re: [OT] SP Cursor

Reply via email to