Paging, Temp Tables, and table variables.. oh, my.

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

SQL Server Development (2000)

Paging, Temp Tables, and table variables.. oh, my.

Author

Topic

Kervin
Starting Member

12 Posts

Posted - 2003-08-14 : 05:18:13

Where to start?...
Hi.
Everything I read gives a different opinion on the 'right' solution to my problem... What do you think?

I am trying to page through a recordset using a stored procedure. The books I have outlined how to do so using a temp table created with its own identity field. Further reading on the web has lead me to believe that this method will become problematic in a multi-user environment and that with large tables the locking and overhead created by using temp tables will slow my web application. Table variable data types - sound like a solution my problem... if only I could get it working. Please feel free to comment on this method of paging, the value of table variables, the overhead caused by using temp tables or ( most importantly ) what I am doing wrong in my code that the following does not work. ( I had it working when I used a temp table ). The Syntax check in SQL says everything is ok. but my asp page returns the following error:

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Must declare the variable '@TempTableVar'.

DECLARE @SQL varchar(8000)
DECLARE @TempTableVar table
(
PageID int IDENTITY,
field1 int NOT NULL,
field2 int NOT NULL,
field3 nvarchar(50) NOT NULL
)
SELECT @SQL = ''
SELECT @SQL = @SQL + 'INSERT INTO @TempTableVar (field1,field2,field3)
SELECT
field1,field2,field3
FROM tablename
WHERE ...'
EXEC(@SQL)
SELECT @iPageCount=COUNT(*) From @TempTableVar -- store total rows
SELECT field1,field2,field3 -- output paged recordset
FROM @TempTableVar
WHERE PageID > @iStart AND PageID < @iEnd

The good guys here at SQLTeam.com provided the example below which I used as a guide.

declare @TableVar table (
NewPK int identity(1,1),
CustomerID nchar(5) NOT NULL )
Insert Into @TableVar (CustomerID)
Select CustomerID
From Customers
Select Top 5 *
from @TableVar

Thankx

Kervin.
kervin_findlay@hotmail.com

SanetteWessels
Starting Member

2 Posts

Posted - 2003-08-14 : 05:37:05

Variable tables can not be seen within dynamic sql. You can rather create the variable table inside the dynamic code. Why are you using dynamic sql in the 1st place?

Sanette

Kervin
Starting Member

12 Posts

Posted - 2003-08-14 : 05:46:32

I need to use dynamic SQL because I need to add string variables (passed into the procedure) to the SQL statement (not shown in my example) ie:
@username -- _joe is passed into the SP
@SQL="SELECT field1,field2,field3
FROM tablename" + @username +" WHERE..."

where the table name in the database is called: tablename_joe

Thankx

Kervin.
kervin_findlay@hotmail.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-14 : 07:20:11

That is NOT a good database design, to have 1 table per user! as you can see, it really complicates your queries.

Instead of:

Table_Joe
Table_Jeff
Table_Rob

create only 1 table, with a field that indicates WHO that row belongs to.

Thus, if the key of the above tables is "ID", then the key of the consolidated table is a combination of "User" and "ID".

Then, your query is re-written as:

select f1,f2,f3 from table where User = @UserName

No dynamic SQL!

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-14 : 07:21:39

And while you are at it, check out:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27297&SearchTerms=paging

for paging records w/o a temp table.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources