SQL Server Performance

Using Cursor Varying OUT

Discussion in 'T-SQL Performance Tuning for Developers' started by suniljk7, Nov 28, 2007.

  1. suniljk7 New Member

    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
  2. ranjitjain New Member

    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
  3. suniljk7 New Member

    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
  4. Madhivanan Moderator

    Do you have identity column?
    Declare @var varchar(8000)
    set @var=''
    Select @var=@var+','+cast(col as varchar(10)) from table where idcol%15=0
    print @var
  5. suniljk7 New Member

    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
  6. Madhivanan Moderator

    Where do you want to show data?
    If you use front end application, do concatenation there
  7. suniljk7 New Member

    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
  8. suniljk7 New Member

    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
  9. ranjitjain New Member

    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.

Share This Page