Using Cursor Varying OUT

I want to write a store proc with one cursor and the output
should be in cursor variable

when each time it moves through the cursor the value will be
added to the cursor variable.
please help me in doing this


CREATE PROCEDURE dbo.SetPaging
@pagecount int,
@brakepoints CURSOR VARYING OUT
AS

SET NOCOUNT ON

DECLARE @idno int, @pageincrement int
SELECT @pageincrement =1
BEGIN
DECLARE CheckResult SCROLL CURSOR FOR
SELECT msgid FROM MOQueues_Test(NOLOCK)
WHERE srvcid=143 ORDER BY msgid
OPEN CheckResult

FETCH ABSOLUTE @pageincrement FROM CheckResult INTO @idno
WHILE(@@FETCH_STATUS = 0)
BEGIN
SET @brakepoints = @brakepoints + ',' + Cast(@idno as varchar(23))
SET @pageincrement = @pageincrement + @pagecount
FETCH ABSOLUTE @pageincrement FROM CheckResult INTO @idno
END

CLOSE CheckResult
DEALLOCATE CheckResult
END
Thanks to all
John

Hi,
Before writing this cursor, I would suggest you to search some good articles here on WAYS TO AVOID CURSOR, and then tell us your required result and then we could help you.
Looking at your work, it looks like, you are just incrementing the variable as making a csv, this can be achieved using a simple select query

Thanks for the reply,
Can you tell me that simple query.. which can get every nth ID from a table which has more than 10000 rows and all the ID should be in a row. why am not using a variable because of its limitation of 8000 char. what i want to achieve is when the user pass an incremental value. say 15. i need to get every 15th id of the table which satisfy the condition in one row. ( like 1st id, 15th id, 30th id, 45th id). i hope now you can help me.
Thanks
John

Thanks for that query.. but if am doing this on a table have 100,000 rows how can this be stored in a 8000 char.. that is why i need to store this in some cursor or is there any other option? please guide me if with your view.
Thanks
John

See there is one issue in your answer, if the id column is not continuous the division will not be correct, also if the query has where condition which may not result in the continuity of id column. Also if we have a continuous ID column without any deletion we can just add the user input an get the values.. so this can't be used. Is there any way we can generate one sequence number along with the query output and upon that column we can do this checking to get the result.
Concatenation also will not help here. the column which we are talking about is an int column which can go till 10 digit so we can't make this a standard.
The plan is for pagination with taking user input.
Thanks
John

dear Ranjit sir,
I hope now you get to know why i used the cursor here without using a simple select statement.. Now please guide me with the possible solutions you have.is it possible to get any a sequence number generated with the rows??
Thanks and waiting for you reply.
John

Hi,
I still don't agree that you need to use cursor here,
When you say, return nth id where n=15
then return ids where it is 1,15,30,45,60 and so on right?
In that case, Query posted by Madhi should work,
Even though you don't have continuous id, but still you would be interested
to return ids matching the criteria. Also if you are restricted with size of varchar, you can split the result in more than one variable.
Another understanding could be that,
you get a resultset with all the filters and from this result you need to return nth ids
based on some ordering.
Here identity values can be generated only by inserting the resultset into table datatype with identity column and then go with madhi's suggestion.