Results 1 to 5 of 5
  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:


    Code:
    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
    Feb 2003
    Posts
    9
    perhaps build it as a var string and parse in that value then execute that var

  3. #3
    Join Date
    Jan 2003
    Posts
    126
    Can you show me a small example?

  4. #4
    Join Date
    Feb 2003
    Posts
    9
    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)

  5. #5
    Join Date
    Jan 2003
    Posts
    126

    Cool

    THANK YOU, THANK YOU, THANK YOU!

    It worked like a charm!

Posting Permissions

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