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="mailto
laylistItemPos=@pos">PlaylistItemPos=@poswhere 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