[dba-SQLServer] SQL Server 2005 - Build a table in a database using a stored procedure

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)

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Saturday, June 02, 2007 1:19 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] SQL Server 2005 - Build a table in a database using
a stored procedure

I am trying to build a table in a database "on demand".  I have a sql
statement (see immediately below) that I got from the "script to query
window wizard".  It works just fine and I have used it several times (to
test it) just changing the "USE database" statement.

USE [MyDatabaseName]
GO
/****** Object:  Table [dbo].[tblDataMapCSVOut]    Script Date: 06/02/2007
04:01:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
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]

GO
SET ANSI_PADDING OFFEND
GO

Works fine.  Now I want to put that in a stored procedure so that I can just
call the sp, pass in the name of the database to create the table in and go.
I created a stored procedure as below:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE sp_CreateDataMapCSVOut
	-- Add the parameters for the stored procedure here
	@DatabaseName	varchar(255)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here USE '[' + @DatabaseName + ']'
GO
/****** Object:  Table [dbo].[tblDataMapCSVOut]    Script Date: 06/02/2007
04:01:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
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]

GO
SET ANSI_PADDING OFFEND
GO


Notice that I am trying to pass in the name of the database and use that
parameter to determine where the table is created.  This does not work fine,
giving me the following errors when I try to run it.

Msg 154, Level 15, State 1, Procedure sp_CreateDataMapCSVOut, Line 16 a USE
database statement is not allowed in a procedure, function or trigger.
Msg 102, Level 15, State 4, Line 1
Incorrect syntax near 'ANSI_PADDING'.

Is there a way to do this?  Can someone "fix me up" here so that this works?

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.472 / Virus Database: 269.8.6/828 - Release Date: 6/1/2007
11:22 AM