|
Daniel cheers for that!
I see what your saying, retrieve all or X all the
time and from that recordset retrieve X number you really need.
It's a work around...
Thanks again..
----- Original Message -----
Sent: Wednesday, February 12, 2003 2:05
PM
Subject: [cfaussie] Re: [OT] SP
Cursor
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:
[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/
|
