Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    126

    Unanswered: Stored Procedure Create Table from Variable

    I want to create a table from a stored procedure where the name is passed to it:

    I have this:
    ************************************************** ********

    CREATE PROCEDURE sp_TP_CreateTable

    (
    @TABLENAME as varchar
    )

    AS

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@TABLENAME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[@TABLENAME]

    CREATE TABLE [dbo].[@TABLENAME] (
    [Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Catalog Version] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Mailed] [float] NULL ,
    [Sales] [money] NULL ,
    [Production $] [int] NOT NULL ,
    [Orders] [float] NULL ,
    [Response] [float] NULL ,
    [Response of Test Control] [int] NOT NULL ,
    [Average Invoice] [float] NULL ,
    [SMP] [float] NULL ,
    [SMP of Test Control] [int] NOT NULL ,
    [Catalog Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Brand] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Drop Date] [smalldatetime] NULL
    ) ON [PRIMARY]
    GO


    But it's createing a table called @TABLENAME How can I get this to work?

    Thanks,

    Ken

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Stored Procedure Create Table from Variable

    This is just a guess as I don't know SQL Server at all well, but I imagine all those horrible [square brackets] are there to show that values are literals, so maybe the correct syntax would be:

    CREATE TABLE [dbo].@TABLENAME (

    ...

    Or maybe I'm totally wrong!

  3. #3
    Join Date
    Jan 2003
    Posts
    126
    Tried that... dbo.@TABLENAME

    I get and error incorrect syntax near @TABLENAME

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by GA_KEN
    Tried that... dbo.@TABLENAME

    I get and error incorrect syntax near @TABLENAME
    Why dbo.@TABLENAME and not [dbo].@TABLENAME ?

  5. #5
    Join Date
    Jan 2003
    Posts
    126

    Unhappy

    I tried it both ways and got the same error message.

    There has got to be a way to do this. I suppose I can use vb to do what I want, but the stored procedure seemed the logical way to do it in the first place.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Found this via Google:

    A common question asked of SQL Team is "How do I write a stored procedure that will create a table/database. I want to pass in the name"

    SQL Server will not allow this

    Create Table @TableName (
    ID int NOT NULL Primary Key,
    FieldName VarChar(10)
    )

    Once again, dynamic SQL to the rescue

    Declare @SQL VarChar(1000)

    SELECT @SQL = 'Create Table ' + @TableName + '('
    SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'

    Exec (@SQL)

    URL: http://www.sqlteam.com/item.asp?ItemID=4619

  7. #7
    Join Date
    Jan 2003
    Posts
    126

    THIS WORKED!

    I tried this, and it worked:

    Code:
    CREATE PROCEDURE sp_TP_CreateTable
    
    (
    @TABLENAME as varchar (50)
    )
    
    AS
    DECLARE @SQL varchar(2000)
    SET @SQL = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@TABLENAME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[@TABLENAME]
    
    CREATE TABLE [dbo].[" + @TABLENAME + "] (
    	[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Catalog Version] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Mailed] [float] NULL ,
    	[Sales] [money] NULL ,
    	[Production $] [int] NOT NULL ,
    	[Orders] [float] NULL ,
    	[Response] [float] NULL ,
    	[Response of Test Control] [int] NOT NULL ,
    	[Average Invoice] [float] NULL ,
    	[SMP] [float] NULL ,
    	[SMP of Test Control] [int] NOT NULL ,
    	[Catalog Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Brand] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Drop Date] [smalldatetime] NULL 
    	) ON [PRIMARY]"
    
    EXEC(@SQL)
    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •