Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Question Unanswered: Crearting a new table via SPROCS

    I supply a parameter @TEMPTABLE for the table name. When I execute my SPROC with the parameter, execution goes thru by I always end up getting "@TEMPTABLE" as the name of the table instead of the value of the parameter.

    Any ideas would be appreciated.

    Thank you.

    Code:
    CREATE PROCEDURE sp_CREATE_TEMP_TABLE  
    (@TEMPTABLE varchar(30))
    AS
    CREATE TABLE @TEMPTABLE (
    	[WOTempID] [int] IDENTITY (1, 1) NOT NULL ,
    	[OrderScheduleID] [int] NULL ,
    	[OrderID] [int] NULL ,
    	[FormCode] [varchar] (4) NULL ,
    	[AcctName] [varchar] (50) NULL ,
    	[AcctRetailer] [varchar] (50) NULL ,
    	[StoreCode] [varchar] (12) NULL ,
    	[RankCode] [varchar] (3) NULL ,
    	[RankID] [tinyint] NULL ,
    	[DeptCode] [int] NULL ,
    	[WeekNo] [tinyint] NULL ,
    	[StartDate] [smalldatetime] NULL ,
    	[EndDate] [smalldatetime] NULL 
    ) ON [PRIMARY]

  2. #2
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53
    Hi,

    your tablename is "@temptable" ... the server will not use the var, instead it will name the table...

    you need to build an cmd-string and execute it. something like this:

    declare @S nvarchar(1000)
    select @S=
    'CREATE TABLE'+@TEMPTABLE+' (
    [WOTempID] [int] IDENTITY (1, 1) NOT NULL ,
    [OrderScheduleID] [int] NULL ,
    [OrderID] [int] NULL ,
    [FormCode] [varchar] (4) NULL ,
    [AcctName] [varchar] (50) NULL ,
    [AcctRetailer] [varchar] (50) NULL ,
    [StoreCode] [varchar] (12) NULL ,
    [RankCode] [varchar] (3) NULL ,
    [RankID] [tinyint] NULL ,
    [DeptCode] [int] NULL ,
    [WeekNo] [tinyint] NULL ,
    [StartDate] [smalldatetime] NULL ,
    [EndDate] [smalldatetime] NULL
    ) ON [PRIMARY] '
    exec sp_executesql @S

    markus

Posting Permissions

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