Dynamic Table Names | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Dynamic Table Names

I have the need to create stored procedures that are called from an ASP page. I’ve pasted the SP’s below. What I need to do is have the table name become a variable. Depending on who is logged in it will create and update the table specific to them. Rather than having "tblTempReportG" it may be "tblTempReportA". The "AreaGroup" at the end also needs to correlate to the final lettter of the table name. —————-
CREATE PROCEDURE sp_CreateReportTableG As CREATE TABLE tblTempReportG (
[dataID] [decimal](18, 0) NULL ,
[districtID] [decimal](18, 0) NULL ,
[authID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[office] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[jobID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[jobPosition] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[unionID] [decimal](18, 0) NULL ,
[TriggerCode] [decimal](18, 0) NULL ,
[triggerDescription] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[triggerStartDate] [datetime] NULL ,
[triggerEndDate] [datetime] NULL ,
[triggerFname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[triggerLname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[triggerEID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[casualFname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[casualLname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[casualEID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[casualStartDate] [datetime] NULL ,
[casualEndDate] [datetime] NULL ,
[AppointmentNumber] [int] NULL ,
[DualAppointment] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Approval] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lastupdate] [smalldatetime] NULL ,
[ipaddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[updateby] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[archivedate] [smalldatetime] NULL ,
[archivereason] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[archiveipaddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[archiveby] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO CREATE PROCEDURE sp_PopulateReportCurrentG As
INSERT INTO [swa-web].[tblTempReportG] (
dataID, districtID, authID, office, jobID, jobPosition, unionID, TriggerCode, triggerDescription, triggerStartDate, triggerEndDate, triggerFname, triggerLname,
triggerEID, casualFname, casualLname, casualEID, casualStartDate, casualEndDate, AppointmentNumber, DualAppointment, Comments, Approval,
lastupdate, ipaddress, updateby)
SELECT [swa-web].[tblCurrentData].dataID, [swa-web].[tblCurrentData].districtID, [swa-web].[tblCurrentData].authID, [swa-web].[tblCurrentData].office, [swa-web].[tblCurrentData].jobID, [swa-web].[tblCurrentData].jobPosition, [swa-web].[tblCurrentData].unionID,
[swa-web].[tblCurrentData].TriggerCode, [swa-web].[tblCurrentData].triggerDescription, [swa-web].[tblCurrentData].triggerStartDate, [swa-web].[tblCurrentData].triggerEndDate,
[swa-web].[tblCurrentData].triggerFname, [swa-web].[tblCurrentData].triggerLname, [swa-web].[tblCurrentData].triggerEID, [swa-web].[tblCurrentData].casualFname, [swa-web].[tblCurrentData].casualLname,
[swa-web].[tblCurrentData].casualEID, [swa-web].[tblCurrentData].casualStartDate, [swa-web].[tblCurrentData].casualEndDate, [swa-web].[tblCurrentData].AppointmentNumber,
[swa-web].[tblCurrentData].DualAppointment, [swa-web].[tblCurrentData].Comments, [swa-web].[tblCurrentData].Approval, [swa-web].[tblCurrentData].lastupdate, [swa-web].[tblCurrentData].ipaddress,
[swa-web].[tblCurrentData].updateby
FROM [swa-web].[tblCurrentData] INNER JOIN
dbo.tblDistricts ON [swa-web].[tblCurrentData].districtID = dbo.tblDistricts.districtID
WHERE (dbo.tblDistricts.AreaGroup = "G")
GO
———————-
See if this helps:http://www.sommarskog.se/dynamic_sql.html —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
That does not seem like a good idea to me – why multiple tables with the same structure, instead of one table with a column identifying the logged in user’s records?
Works great! Thanks
Because it is a MASSIVE amount of data for the particular report. I create the temp table to cache the data so it doesn’t have to be calculated and multiple tables joined, etc… each time they want to view the report.
]]>