How to avoid those empty result sets ?

  • Hi all,

    I have a stored procedure which, first, is using a cursor and a fetch loop to update particular data, this is followed by a select statement to return those data.

    It appears that for each fetch next, an empty resultset is sent to the client which has to use a number of SQLMoreResults to get to the interesting part. This is not such a big deal, but it is quite annoying . Is there a way to prevent those empty resultsets for being sent over ?

    Thank you

     

    Patrick Duflot

  • Maybe not what you want to hear, but don't use a cursor.

    Can you post the SP so that we can provide alternative solution to the cursor and check for small syntaxe error?

  • Here is the code of the sp. its goal is to linearly distribute positions across the range [0..INT_MAX[ while maintaining 25% free slots at the end of the range.

    create procedure RedistributePos @IdPlaylist int

    as

    declare @nb-2 int

    declare @offset int

    declare @pos int

    declare @curid int

    declare @curpos int

    declare curPlaylist cursor local static

    for

      select playlistitem.Id, playlistitem.playlistitempos

      from playlistitem inner join playlist on playlist.id = playlistitem.idplaylist

      where playlist.id = @IdPlaylist

      order by playlistitempos

    select @nb-2 = count(PlaylistItem.Id)

    from playlistitem inner join playlist on playlist.id = playlistitem.idplaylist

    where playlist.id = @IdPlaylist

    select @offset = 2147483647 / (@nb+@nb/4)

    if @offset <= 1

    begin

      raiserror ('Playlist @IdPlaylist is full', 10, 1)

    end

    else

    begin

      select @pos = @offset

      open curPlaylist

      fetch next from curPlaylist into @curid, @curpos

     

      while @@FETCH_STATUS = 0

      begin

        update PlaylistItem set <A href="mailtolaylistItemPos=@pos">PlaylistItemPos=@pos

        where PlaylistItem.id = @curid

        select @pos = @pos+@offset

        fetch next from curPlaylist into @curid, @curpos

      end

      close curPlaylist

      deallocate curPlaylist

    end

    select playlistitem.id, playlistitem.playlistitempos

    from playlistitem inner join playlist on playlist.id = playlistitem.idplaylist

    where playlist.id = @IdPlaylist

    order by playlistitempos

    GO

    Patrick Duflot

  • Spent a while trying to figure out what the heck you were talking about with  "empty result sets", but now I think I know.  Try adding this magical little line somewhere near the top of your stored procedure:

    SET NOCOUNT ON

    Good luck.

    - john

  • This is what i was looking for. Thanks John.

    There are a bunch of set options I am not aware of. I should have a look at those...

    Patrick Duflot

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply