Removing blanks from my looped queries

Last Post 10 Apr 2002 11:46 AM by Rich_bailey. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

10 Apr 2002 11:46 AM

I have the following query, which uses a cursor to create seperated record sets for a report. However, I have run into a problem in that it is pulling up blanks for some users since they do not fit into the criteria but are part of the cursor.

Can anyone tell me how to have the cursor/query not display anything if the 'Name' does not have any results based on the rest of the query, but is still part of the cursor list.

I have Included an example of the data the query displays below the query. Not the 0 results blank to which I am refering.

DECLARE @name varchar(32)
DECLARE name_cursor CURSOR FOR SELECT DISTINCT clientusername FROM usernames
ORDER BY clientusername
OPEN name_cursor
FETCH NEXT FROM name_Cursor INTO @name
SELECT TOP 5 s.clientusername AS Name, count(*) as Hits,
S.desthost AS DestinationSite FROM Sitelog S WHERE S.clientusername = @name
and s.desthost in (select desthost from desthost)
GROUP BY S.clientusername,S.desthost ORDER BY Hits desc
CLOSE name_cursor DEALLOCATE name_cursor

Name Hits DestinationSite
-------------------------------- ----------- --------------------
Domain\BOB 30
Domain\BOB 28
Domain\BOB 18
Domain\BOB 7
Domain\BOB 7

(5 row(s) affected)

Name Hits DestinationSite
-------------------------------- ----------- --------------------
(0 row(s) affected)

Name Hits DestinationSite
-------------------------------- ----------- ---------------------------
Domain\SARAH 542
Domain\SARAH 117
Domain\SARAH 79
Domain\SARAH 43
Domain\SARAH 36
(5 row(s) affected)

Acceptable Use Policy