• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

how to dynamically create a temp table, then access it for paging

Hi all,

I have a stored procedure that does paging utitilizing a stored procedure.
Because the parameters are created on the fly, it must use dynamic SQL.

Here is the code (doesn't work):

CREATE PROCEDURE dbo.GetRecsByPage
(
      @Page int,
      @RecsPerPage int,
      @SortBy varchar(15),
      @SortOrder varchar(4),
      @TableName varchar(30), --name of table to page
      @DistinctField varchar(30), --name of column for a distinct field
      @FieldList varchar(1000), --a list of all the columns
      @TTableDef varchar(2000) --the columns with definitions for the temp table (ie name char(50), )
)

AS
SET NOCOUNT ON
--create temp table
EXEC  ('CREATE TABLE #TempItems ( IDx int IDENTITY, '+@TTableDef+')')
-- insert values into temp table
EXEC ( 'INSERT INTO #TempItems ('+@FieldList+') SELECT '+@FieldList+' FROM ' + @TableName+ ' ORDER BY ' + @SortBy + ' ' + @SortOrder)

DECLARE @FirstRec int, @LastRec int
SET @FirstRec = (@Page - 1) * @RecsPerPage
SET @LastRec = (@Page * @RecsPerPage + 1)

EXEC ('SELECT MoreRecords = (SELECT COUNT(*)  FROM #TempItems TI WHERE TI.IDx >= ' + @LastRec +
'), TotRecCount = (SELECT COUNT(*) FROM #TempItems), UniqueRecCount=(SELECT COUNT(DISTINCT '+ @DistinctField +
') FROM #TempItems),' + @FieldList + ' FROM #TempItems WHERE IDx > ' + @FirstRec + ' AND IDx < ' + @LastRec)

SET NOCOUNT OFF
GO


The temp table is not defined within the same scope, how can I write this so that it is in the same scope?


0
simplyamazing
Asked:
simplyamazing
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
rafranciscoCommented:
Simply make your temp table global by using 2 pound signs (##).

CREATE PROCEDURE dbo.GetRecsByPage
(
     @Page int,
     @RecsPerPage int,
     @SortBy varchar(15),
     @SortOrder varchar(4),
     @TableName varchar(30), --name of table to page
     @DistinctField varchar(30), --name of column for a distinct field
     @FieldList varchar(1000), --a list of all the columns
     @TTableDef varchar(2000) --the columns with definitions for the temp table (ie name char(50), )
)

AS
SET NOCOUNT ON
--create temp table
EXEC  ('CREATE TABLE ##TempItems ( IDx int IDENTITY, '+@TTableDef+')')
-- insert values into temp table
EXEC ( 'INSERT INTO ##TempItems ('+@FieldList+') SELECT '+@FieldList+' FROM ' + @TableName+ ' ORDER BY ' + @SortBy + ' ' + @SortOrder)

DECLARE @FirstRec int, @LastRec int
SET @FirstRec = (@Page - 1) * @RecsPerPage
SET @LastRec = (@Page * @RecsPerPage + 1)

EXEC ('SELECT MoreRecords = (SELECT COUNT(*)  FROM ##TempItems TI WHERE TI.IDx >= ' + @LastRec +
'), TotRecCount = (SELECT COUNT(*) FROM ##TempItems), UniqueRecCount=(SELECT COUNT(DISTINCT '+ @DistinctField +
') FROM ##TempItems),' + @FieldList + ' FROM ##TempItems WHERE IDx > ' + @FirstRec + ' AND IDx < ' + @LastRec)

SET NOCOUNT OFF
GO
0
 
simplyamazingAuthor Commented:
Ok, this is how it looks now and it works fine, BUT, I'm worried about another user running the same routine and changing a different users table or deleting it since they would all have the same name. In other words, 100 people could be running this at the same time.  
I had to put in a DROP TABLE in this or else the temp table would still be there between executions (what I ran into using the query analyzer).


CREATE PROCEDURE dbo.GetRecsByPage
(
      @Page int,
      @RecsPerPage int,
      @SortBy varchar(15),
      @SortOrder varchar(4),
      @TableName varchar(30),
      @DistinctField varchar(30),
      @FieldList varchar(1000),
      @TTableDef varchar(2000)
)

AS
SET NOCOUNT ON

--test for existence of table
if exists (select top 1 * from dbo.sysobjects where id = object_id(N'##TempItems') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE ##TempItems

--create temp table
EXEC  ('CREATE TABLE ##TempItems (IDx int IDENTITY, '+@TTableDef+')')

-- insert values into temp table
EXEC ( 'INSERT INTO ##TempItems ('+@FieldList+') SELECT '+@FieldList+' FROM ' + @TableName+ ' ORDER BY ' + @SortBy + ' ' + @SortOrder)

DECLARE @FirstRec int, @LastRec int
SET @FirstRec = (@Page - 1) * @RecsPerPage
SET @LastRec = (@Page * @RecsPerPage + 1)

EXEC ('SELECT MoreRecords = (SELECT COUNT(*)  FROM ##TempItems TI WHERE TI.IDx >= ' + @LastRec +
'), TotRecCount = (SELECT COUNT(*) FROM ##TempItems), UniqueRecCount=(SELECT COUNT(DISTINCT '+ @DistinctField +
') FROM ##TempItems),' + @FieldList + ' FROM ##TempItems WHERE IDx > ' + @FirstRec + ' AND IDx < ' + @LastRec)

DROP TABLE ##TempItems

SET NOCOUNT OFF
GO
0
 
doobdaveCommented:
You should not have this problem, as temporary tables with a global scope only remain in scope on a per-conneciton basis, AFAIK.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
rafranciscoCommented:
Here's what I've got from Book Online regarding global temporary tables:

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
0
 
Scott PletcherSenior DBACommented:

Maybe do all the processing within a single EXEC:

DECLARE @FirstRec int, @LastRec int
SET @FirstRec = (@Page - 1) * @RecsPerPage
SET @LastRec = (@Page * @RecsPerPage + 1)

EXEC  ('CREATE TABLE #TempItems ( IDx int IDENTITY, '+@TTableDef+')' + ' ' +
    'INSERT INTO #TempItems ('+@FieldList+') SELECT '+@FieldList+' FROM ' + @TableName+ ' ORDER BY ' + @SortBy + ' ' + @SortOrder + ' ' +
    'SELECT MoreRecords = (SELECT COUNT(*)  FROM #TempItems TI WHERE TI.IDx >= ' + @LastRec +
'), TotRecCount = (SELECT COUNT(*) FROM #TempItems), UniqueRecCount=(SELECT COUNT(DISTINCT '+ @DistinctField +
') FROM #TempItems),' + @FieldList + ' FROM #TempItems WHERE IDx > ' + @FirstRec + ' AND IDx < ' + @LastRec)
0
 
simplyamazingAuthor Commented:
"...Global temporary tables are automatically dropped when the session that created the table ends..."
"...The association between a task and a table is maintained only for the life of a single Transact-SQL statement..."
I thought this to be true too, but eliminating the DROP TABLE(s) from the procedure will leave the global temp table in memory - I tested it by running the sp in Query Analyzer, then running it again - the second time generates an error saying the table already exists - meaning that even though the sp is run again, the table created before is still there.  
Perhaps because the global temp table was created via invoking the EXEC command and not directly, it is not dropped upon completion of the sp - but rather must be dropped via "DROP TABLE" code.  This would explain my results.

Scott's suggestion looks the way to go ... why didn't I think of this? ;)

0
 
rafranciscoCommented:
>> I thought this to be true too, but eliminating the DROP TABLE(s) from the procedure will leave the global temp table in memory - I tested it by running the sp in Query Analyzer, then running it again - the second time generates an error saying the table already exists - meaning that even though the sp is run again, the table created before is still there. <<

When you ran the sp again in QA, did you close your session or did you run from the same session?  If it was from the same session, then the global temp table will not be dropped.  You have to close the session for it to be dropped.
0
 
Scott PletcherSenior DBACommented:
Global temp tables really can cause issues when more than one person is trying to use them.
0
 
simplyamazingAuthor Commented:
Ah, so the "session" remains open - is that the same as the connection? if so, global temp tables would be an extremely bad idea for websites that utilize the same connection over and over.

Anyway, Scott's idea worked perfectly and there is no mystery as to the scope/session/naming/etc of the created table, but I improved it some by using a TABLE variable instead.  Here is what I have now: (complete)

CREATE PROCEDURE dbo.GetRecsByPage
(
      @Page int,
      @RecsPerPage int,
      @SortBy varchar(15),
      @SortOrder varchar(4),
      @TableName varchar(30),
      @DistinctField varchar(30),
      @FieldList varchar(200),
      @TTableDef varchar(400)
)

AS
SET CONCAT_NULL_YIELDS_NULL OFF;
SET NOCOUNT ON;

DECLARE @FirstRec int, @LastRec int
SET @FirstRec = (@Page - 1) * @RecsPerPage
SET @LastRec = (@Page * @RecsPerPage + 1)

EXEC  ('DECLARE @TempItems TABLE ( IDx int IDENTITY, '+@TTableDef+')' + ' ' +
    'INSERT INTO @TempItems ('+@FieldList+') SELECT '+@FieldList+' FROM ' + @TableName+ ' ORDER BY ' + @SortBy + ' ' + @SortOrder + ' ' +
    'SELECT MoreRecords = (SELECT COUNT(*)  FROM @TempItems TI WHERE TI.IDx >= ' + @LastRec +
'), TotRecCount = (SELECT COUNT(*) FROM @TempItems), UniqueRecCount=(SELECT COUNT(DISTINCT '+ @DistinctField +
') FROM @TempItems),' + @FieldList + ' FROM @TempItems WHERE IDx > ' + @FirstRec + ' AND IDx < ' + @LastRec)

SET NOCOUNT OFF;
GO



Example Usage:
GetRecsByPage 54,
100,
'firstvisit',
'DESC',
'emails',
'emailaddress','emailaddress,firstname,lastname,ipaddress,firstvisit,emaillist,vemaillist,removerequest,lastemailing,mailingid',
'emailaddress varchar(50),emaillist char(1),vemaillist char(1),firstname varchar(50),lastname varchar(50),removerequest char(1),firstvisit datetime,lastemailing datetime,mailingid varchar(10),ipaddress varchar(15)'


Fairly general purpose now.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now