Select Top var. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select Top var.

Author

Topic

davidshq
Posting Yak Master

119 Posts

Posted - 2005-11-19 : 17:06:02

I have a table with a variety of rows in it categorized into categories such as Active Inside, Semi-Active Inside, Inactive Inside. I then have a configuration tool which allows a person to select how many of each category they want to see (e.g., top 3 of Active Inside, top 2 of Semi-Active Inside, and top 0 of Inactive Inside). I have it set up currently that these rows are selected into a temporary table and then the aggregates are output. The problem is when the rowcount is set to 0 it selects all of the games instead of none of them. I tried a If...Then statement but I haven't been able to get that to work either. Here is my current stored procedure:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[SelectWeeklyGames]
(
@varUserName varchar(200),
@varAI int,
@varSI int,
@varII int,
@varCG int,
@varAO int,
@varSO int,
@varIO int,
@varWG int,
@varMG int,
@varTrick int
)
AS
select uID, gID, Popularity as Popularity2 into #temppopularity from GamePopularity WHERE uID LIKE @varUserName
select uID as uID2, gID as gID2, LastPlayedDate into #templastplayed from LastPlayed WHERE uID LIKE @varUserName
If @varAI > 0
BEGIN
SET ROWCOUNT @varAI
select * into #results
FROM Game G
LEFT OUTER JOIN #templastplayed LP
On G.ID=LP.gID2
LEFT JOIN #temppopularity GP
On G.ID=GP.gID
WHERE G.Type=1 AND (LP.gID2 IS NULL OR ((LP.LastPlayedDate < DATEADD(mm,-1,getDate()))
AND (LP.uID2 LIKE @varUserName)))
AND ((GP.gID=G.ID AND GP.uID LIKE @varUserName) OR GP.gID IS NULL)
END
If @varSI > 0
BEGIN
SET ROWCOUNT 0
set rowcount @varSI
Insert into #Results
select *
FROM Game G
LEFT OUTER JOIN #templastplayed LP
On G.ID=LP.gID2
LEFT JOIN #temppopularity GP
On G.ID=GP.gID
WHERE G.Type=2 AND (LP.gID2 IS NULL OR ((LP.LastPlayedDate < DATEADD(mm,-1,getDate()))
AND (LP.uID2 LIKE @varUserName)))
AND ((GP.gID=G.ID AND GP.uID LIKE @varUserName) OR GP.gID IS NULL)
END
If @varII > 0
BEGIN
SET ROWCOUNT 0
set rowcount @varII
Insert into #Results
select *
FROM Game G
LEFT OUTER JOIN #templastplayed LP
On G.ID=LP.gID2
LEFT JOIN #temppopularity GP
On G.ID=GP.gID
WHERE G.Type=3 AND (LP.gID2 IS NULL OR ((LP.LastPlayedDate < DATEADD(mm,-1,getDate()))
AND (LP.uID2 LIKE @varUserName)))
AND ((GP.gID=G.ID AND GP.uID LIKE @varUserName) OR GP.gID IS NULL)
END
SET ROWCOUNT 0
set rowcount @varCG
Insert into #Results
select *
FROM Game G
LEFT OUTER JOIN #templastplayed LP
On G.ID=LP.gID2
LEFT JOIN #temppopularity GP
On G.ID=GP.gID
WHERE G.Type=4 AND (LP.gID2 IS NULL OR ((LP.LastPlayedDate < DATEADD(mm,-1,getDate()))
AND (LP.uID2 LIKE @varUserName)))
AND ((GP.gID=G.ID AND GP.uID LIKE @varUserName) OR GP.gID IS NULL)
SET ROWCOUNT 0
set rowcount @varAO
Insert into #Results
select *
FROM Game G
LEFT OUTER JOIN #templastplayed LP
On G.ID=LP.gID2
LEFT JOIN #temppopularity GP
On G.ID=GP.gID
WHERE G.Type=5 AND (LP.gID2 IS NULL OR ((LP.LastPlayedDate < DATEADD(mm,-1,getDate()))
AND (LP.uID2 LIKE @varUserName)))
AND ((GP.gID=G.ID AND GP.uID LIKE @varUserName) OR GP.gID IS NULL)
SET ROWCOUNT 0
set rowcount @varSO
Insert into #Results
select *
FROM Game G
LEFT OUTER JOIN #templastplayed LP
On G.ID=LP.gID2
LEFT JOIN #temppopularity GP
On G.ID=GP.gID
WHERE G.Type=6 AND (LP.gID2 IS NULL OR ((LP.LastPlayedDate < DATEADD(mm,-1,getDate()))
AND (LP.uID2 LIKE @varUserName)))
AND ((GP.gID=G.ID AND GP.uID LIKE @varUserName) OR GP.gID IS NULL)
SET ROWCOUNT 0
set rowcount @varIO
Insert into #Results
select *
FROM Game G
LEFT OUTER JOIN #templastplayed LP
On G.ID=LP.gID2
LEFT JOIN #temppopularity GP
On G.ID=GP.gID
WHERE G.Type=7 AND (LP.gID2 IS NULL OR ((LP.LastPlayedDate < DATEADD(mm,-1,getDate()))
AND (LP.uID2 LIKE @varUserName)))
AND ((GP.gID=G.ID AND GP.uID LIKE @varUserName) OR GP.gID IS NULL)
SET ROWCOUNT 0
set rowcount @varWG
Insert into #Results
select *
FROM Game G
LEFT OUTER JOIN #templastplayed LP
On G.ID=LP.gID2
LEFT JOIN #temppopularity GP
On G.ID=GP.gID
WHERE G.Type=8 AND (LP.gID2 IS NULL OR ((LP.LastPlayedDate < DATEADD(mm,-1,getDate()))
AND (LP.uID2 LIKE @varUserName)))
AND ((GP.gID=G.ID AND GP.uID LIKE @varUserName) OR GP.gID IS NULL)
SET ROWCOUNT 0
set rowcount @varMG
Insert into #Results
select *
FROM Game G
LEFT OUTER JOIN #templastplayed LP
On G.ID=LP.gID2
LEFT JOIN #temppopularity GP
On G.ID=GP.gID
WHERE G.Type=9 AND (LP.gID2 IS NULL OR ((LP.LastPlayedDate < DATEADD(mm,-1,getDate()))
AND (LP.uID2 LIKE @varUserName)))
AND ((GP.gID=G.ID AND GP.uID LIKE @varUserName) OR GP.gID IS NULL)
SET ROWCOUNT 0
set rowcount @varTrick
Insert into #Results
select *
FROM Game G
LEFT OUTER JOIN #templastplayed LP
On G.ID=LP.gID2
LEFT JOIN #temppopularity GP
On G.ID=GP.gID
WHERE G.Type=10 AND (LP.gID2 IS NULL OR ((LP.LastPlayedDate < DATEADD(mm,-1,getDate()))
AND (LP.uID2 LIKE @varUserName)))
AND ((GP.gID=G.ID AND GP.uID LIKE @varUserName) OR GP.gID IS NULL)
SET ROWCOUNT 0
select * from #results ORDER BY Type ASC, Popularity DESC, Popularity2 DESC
/* SET NOCOUNT ON */
RETURN

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

Kristen
Test

22859 Posts

Posted - 2005-11-20 : 03:01:57

You need to pre-create your table. Your first "test" does:

If @varAI > 0
BEGIN
SET ROWCOUNT @varAI
select * into #results
FROM Game G
...

whereas the rest do

Insert into #Results
select *
FROM Game G
...

so if that first test "@varAI > 0" is zero the table won't be created.

Its better (performance wise) to use CREATE TABLE anyway - and put a primary key on it whilst you are at it, but if you need to be more "flexible" just put

select *
into #results
FROM Game G
LEFT OUTER JOIN ... etc ...
WHERE 1=0

in front of your specific tests.

By the by, all the SELECT * is frowned on - do you need ALL the columns? And ALL the columns that you might add to those tables in the future? What about if you add some massive TEXT columns in the future? That would crucify the performance of your query at that time!

There is also a performance issue that SQL has to work out what "*" means each time the query is run, and I believe will therefore not cache the query plan.

If not list only the columns ones that you do need.

Or compromise and do SELECT G.*, LP.Col1, LP.Col2, GP.ColA

to shorten the list

Kristen

davidshq
Posting Yak Master

119 Posts

Posted - 2005-11-20 : 09:32:20

Kristen,
Thanks for your thoughts and suggestions, you are absolutely right and my code is now rectified. Thanks also for the hints on optimizing my SQL, I will definetly try to put some of those tips into place in the near future. If you'd like to see the finished product of the above work it is at http://www.gamesecretary.com/. Thanks again for your help.
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources