Robert L. Stewart
rl_stewart at highstream.net
Mon Jun 4 08:59:58 CDT 2007
You almost have it correct. You cannot issue a USE statement. So do not do that portion of the code. SET @Sql = 'CREATE TABLE ' + @DatabaseName + '.dbo.tblDataMapCVSOut.... is the only change to the rest of the code Eric sent to you. Robert At 12:00 PM 6/3/2007, you wrote: >Date: Sat, 02 Jun 2007 12:20:08 -0700 >From: "Eric Barro" <ebarro at verizon.net> >Subject: Re: [dba-SQLServer] SQL Server 2005 - Build a table in a > database using a stored procedure >To: <dba-sqlserver at databaseadvisors.com> >Message-ID: <0JJ000CIZWE3NC80 at vms048.mailsrvcs.net> >Content-Type: text/plain; charset=us-ascii > >This is what's referred to as dynamic SQL in a sproc.... > >First declare a variable to hold the commands you want to execute > >DECLARE @sql varchar(4000) >--assign the use command to the variable >SET @sql = 'USE ' + @DatabaseName >--execute the command >EXEC (@sql) >--assign the create command to the variable >SET @sql = 'CREATE TABLE [dbo].[tblDataMapCSVOut]( > [CSVID] [int] IDENTITY(1,1) NOT NULL, > [CSVFldNameInTable] [varchar](50) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL, > [CSVFldNameInFile] [varchar](50) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL >) ON [PRIMARY]' > >EXEC (@sql)