Should I use a temp table? - 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 2005 Forums

Transact-SQL (2005)

Should I use a temp table?

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2008-01-24 : 00:30:52

I have a table which holds all the needed information of all the images on my site for my different image gallery's. Here is the table crations code:

CREATE TABLE [Eaglef90].[Images](
	[ImageName] [varchar](50) NOT NULL,
	[Game] [varchar](15) NOT NULL,
	[SubSet] [varchar](15) NOT NULL,
	[Width] [smallint] NOT NULL,
	[Height] [smallint] NOT NULL,
	[AltText] [varchar](50) NOT NULL,
	[ImageNumber] [smallint] IDENTITY(1,1) NOT NULL

What I want to do is pull 20 rows at a time from this table based on a where clause, so I can create a page that only has images from a certain game and subset. Since some games/subset will have more then 20 images more then one page will be used (like a tipical image gallery would do). The problem is I can not figure out the best way to write the select statement for Perl to use. I was thinking about putting everything that matched the game/subset conditions into a temp table and working form that but thought that might not be the best way so I wanted to come here and ask. Any suggestions would be nice.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-24 : 00:35:26

see http://www.sqlteam.com/article/server-side-paging-using-sql-server-2005

KH
[spoiler]Time is always against us[/spoiler]

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-01-24 : 06:46:37

There are many ways you complete this task . As you are using sql 2005 - consider using the ROW NUMBER function

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2008-01-24 : 11:06:15

Thanks so much, that aritical (which talks about using the ROW NUMBER function) looks like the trick.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2008-01-24 : 19:30:59

Ok, follow up question. I wrote this query:

with ImageGallery as
(select ImageName, AltText, Row_Number() over (order by ImageNumber ASC) as RowNumber
from Eaglef90.Images where Game = $Game and SubSet = $SubSet)
select RowNumber, ImageName, AltText from ImageGallery where RowNumber Between $LowerLimit and $UpperLimit order by RowNumber ASC

But how would I know the number of the last row inorder to know how many pages to link to?

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources