August 22, 2016 at 5:14 am
Hello all
I am simply stumped on this very simple task and just can't get it to work at all, I have Googled this also taking the CAST advice and various parts but all to no avail.
I am trying to dynamically create a table using a CREATE TABLE script which is wrapped in dynamic SQL so I can pass the table name.:
Here is my proc:
[font="Courier New"]CREATE PROCEDURE [dbo].[usp_CreatePeriodRebateTable]
(
@TableName VARCHAR(50)
)
AS
BEGIN
DECLARE @ExecSQL VARCHAR(MAX)
SET @ExecSQL ='CREATE TABLE '+@TableName+'
(
[FK_Product_Id] [int] NOT NULL,
[FK_Branch_Id] [int] NOT NULL,
[FK_Currency_Id] [int] NOT NULL,
[FK_Period_Id] [int] NOT NULL,
[FK_Brand_Id] [int] NOT NULL,
[FK_Product_Source_Id] [int] NOT NULL,
[Branch_Type] [varchar](6) NOT NULL,
[Units] [int] NOT NULL,
[RatePerUnit] [decimal](18, 9) NULL,
[Product_Id] [varchar](15) NOT NULL,
[UOM] [varchar](5) NOT NULL,
[Rebate_Local] [decimal](18, 9) NULL,
[Rebate_GBP] [decimal](18, 9) NULL,
[Rebate_USD] [decimal](18, 9) NULL,
[Rebate_Euro] [decimal](18, 9) NULL,
[Ext_Rebate_Local] [decimal](18, 9) NULL,
[Ext_Rebate_GBP] [decimal](18, 9) NULL,
[Ext_Rebate_USD] [decimal](18, 9) NULL,
[Ext_Rebate_Euro] [decimal](18, 9) NULL
) ON [PRIMARY]'
PRINT @ExecSQL-- @TableName
EXECUTE @ExecSQL
SET ANSI_PADDING OFF
END
[/font]
--When I run this command.
[font="Courier New"]exec usp_CreatePeriodRebateTable 'DW.Fct_Rebates1608'[/font]
--I get this result the table print looks okay but the dynamic sql runs out of steam?
--0Any help greatly received.
[font="Courier New"]CREATE TABLE DW.Fct_Rebates1608
(
[FK_Product_Id] [int] NOT NULL,
[FK_Branch_Id] [int] NOT NULL,
[FK_Currency_Id] [int] NOT NULL,
[FK_Period_Id] [int] NOT NULL,
[FK_Brand_Id] [int] NOT NULL,
[FK_Product_Source_Id] [int] NOT NULL,
[Branch_Type] [varchar](6) NOT NULL,
[Units] [int] NOT NULL,
[RatePerUnit] [decimal](18, 9) NULL,
[Product_Id] [varchar](15) NOT NULL,
[UOM] [varchar](5) NOT NULL,
[Rebate_Local] [decimal](18, 9) NULL,
[Rebate_GBP] [decimal](18, 9) NULL,
[Rebate_USD] [decimal](18, 9) NULL,
[Rebate_Euro] [decimal](18, 9) NULL,
[Ext_Rebate_Local] [decimal](18, 9) NULL,
[Ext_Rebate_GBP] [decimal](18, 9) NULL,
[Ext_Rebate_USD] [decimal](18, 9) NULL,
[Ext_Rebate_Euro] [decimal](18, 9) NULL
) ON [PRIMARY]
Msg 203, Level 16, State 2, Procedure usp_CreatePeriodRebateTable, Line 37
The name 'CREATE TABLE DW.Fct_Rebates1608
(
[FK_Product_Id] [int] NOT NULL,
[FK_Branch_Id] [int] NOT NULL,
[FK_Currency_Id] [int] NOT NULL,
[FK_Period_Id] [int] NOT NULL,
[FK_Brand_Id] [int] NOT NULL,
[FK_Product_Source_Id] [int] NOT NULL,
[Branch_Type] [varchar](6) NOT NULL,
[Units] [int] NOT NULL,
[RatePerUnit] [decimal](18, 9) NULL,
[Product_Id] [varchar](15) NOT NULL,
[UOM] [varchar](5) NOT NULL,
[Rebate_Local] [decimal](18, 9) NULL,
[Rebate_GBP] [decimal](18, 9) NULL,
[Rebate_USD] [decimal](18, 9) NULL,
[Rebate_Euro] [decimal](18, 9) NULL,
[Ext_Rebate_Local] [decimal](18, 9) NULL,
[Ext_Rebate_GBP] [decimal' is not a valid identifier.
[/font]
August 22, 2016 at 5:21 am
EXECUTE (@ExecSQL)
Without the brackets, the string is assumed to be a stored procedure name.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2016 at 5:24 am
Thanks Gail discovered the answer also about 30 seconds ago. Thank you again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply