Creating and Dropping Temorary Tables

[Zukkster]

I'm trying to create a user specific table. The table is used in a process to hold data before carrying out further calcualtions. Because multiple users might be running the process the table needs to be user specific.

I started off with a stored procedure that created a permanent table, but the thought it would be better to use a temporary table so that the table is dropped automatically once a user session is over.

Because the table is going to be referenced by other stored procedures I want to make it global. The code below works fine until I add the ## to the table name, then I get an error;

"Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named '##tblCouponDetail_Admin' in the database."

I've put in a drop table statement before the create table, which works when I'm doing this with a table called 'tblCouponDetail_Admin'. Here's my code.

USE Reporter
IF EXISTS (SELECT name FROM sysobjects
WHERE name ='USP_CreateUserReportTable')
DROP PROCEDURE USP_CreateUserReportTable
GO

CREATE PROCEDURE USP_CreateUserReportTable (@UserID varchar(20))
AS

DECLARE @TableName nvarchar(30)
DECLARE @strSQL1 NVARCHAR(2000)
DECLARE @strSQL2 NVARCHAR(2000)
DECLARE @strSQL3 NVARCHAR(2000)
DECLARE @LongTableName nvarchar(38)

SET @TableName = '##tblCouponDetail_'+ @UserID

SET @LongTableName = '[dbo].[' + @TableName + ']'

SET QUOTED_IDENTIFIER ON

set @strSQL1 = "if exists (select * from sysobjects where id = object_id(N'" + @LongTableName + "') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table" + @LongTableName


SET @strSQL2 = 'CREATE TABLE ' + @TableName + ' (
[ContractNumber] [int] NULL ,
[NCHCode] [char] (10) NULL ,
[CouponBarcode] [char] (12) NULL ,
[Description] [nvarchar] (50) NULL ,
[Segment] [nvarchar] (50) NULL ,
[CouponType] [nvarchar] (50) NULL ,
[PrintingMCLU] [int] NOT NULL ,
[CouponValue] [money] NULL ,
[RequiredSpend] [money] NULL ,
[RedemptionMCLU] [int] NULL ,
[EstimatedRedemption] [real] NULL ,
[PromotionPeriod] [nvarchar] (50) NULL)
ON [PRIMARY]'

set @strSQL3 = 'ALTER TABLE [dbo].[tblCouponDetail_' + @UserID + '] WITH NOCHECK ADD
CONSTRAINT [PK_tblCouponDetail_' + @UserID + '] PRIMARY KEY NONCLUSTERED
(
[PrintingMCLU]
) ON [PRIMARY]'

EXECUTE(@strSQL1)
EXECUTE(@strSQL2)
EXECUTE(@strSQL3)

SET QUOTED_IDENTIFIER OFF

 

[LFCfan]

Hi there

"##" denotes the temporary table is a global one, ie the same table can be viewed by all connections

Try using just a single "#" instead : #MyTempTable, for a local temporary table
Only the connection that creates this can view it. It is created in tempdb with a name like MyTempTable___________12345 which will be unique for each user


Hope this helps

 

[Zukkster]

That's a great help. I need it to be a global temporary table because I want other stored procedures.

However I didn't know that the temporary table was created in the tempdb, so I've changed my code to include "use tempdb" in the line that drops the table if it already exists (as well as creating the index in the same statement as the CREATE TABLE, which is a bit tidier)

It now runs smoothly. The new code is...

USE Reporter
IF EXISTS (SELECT name FROM sysobjects
WHERE name ='USP_CreateUserReportTable2')
DROP PROCEDURE USP_CreateUserReportTable2
GO

CREATE PROCEDURE USP_CreateUserReportTable2 (@UserID varchar(20))
AS

DECLARE @TableName nvarchar(30)
DECLARE @strSQL1 NVARCHAR(2000)
DECLARE @strSQL2 NVARCHAR(2000)
DECLARE @strSQL3 NVARCHAR(2000)
DECLARE @LongTableName nvarchar(38)

SET @TableName = '##tblCouponDetail_'+ @UserID

SET @LongTableName = '[dbo].[' + @TableName + ']'

SET QUOTED_IDENTIFIER ON

set @strSQL1 = "use tempdb if exists (select * from sysobjects where id = object_id(N'" + @LongTableName + "') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table" + @LongTableName


SET @strSQL2 = 'CREATE TABLE ' + @TableName + ' (
[ContractNumber] [int] NULL ,
[NCHCode] [char] (10) NULL ,
[CouponBarcode] [char] (12) NULL ,
[Description] [nvarchar] (50) NULL ,
[Segment] [nvarchar] (50) NULL ,
[CouponType] [nvarchar] (50) NULL ,
[PrintingMCLU] [int] NOT NULL ,
[CouponValue] [money] NULL ,
[RequiredSpend] [money] NULL ,
[RedemptionMCLU] [int] NULL ,
[EstimatedRedemption] [real] NULL ,
[PromotionPeriod] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_tblCouponDetail_' + @UserID + '] PRIMARY KEY NONCLUSTERED
(
[PrintingMCLU]
) ON [PRIMARY]
) ON [PRIMARY]'

EXECUTE(@strSQL1)
EXECUTE(@strSQL2)

SET QUOTED_IDENTIFIER OFF