I have managed use a stored procedure to create a copy of a table with a variable name. But I am struggling to understand how to incorporate a constraint into the stored procedure.
The problem: the constraint must be a variable as when it makes a copy of a table it cannot name the PK that I have with the same as one used before. I am getting syntax errors in these areas.
I am very new to SQL Server... literally just started learning today!! So please explain in dummy terms.
Code so far below:
CREATE PROCEDURE procFinancialPeriodTable (@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] BEGIN SELECT @SQL = 'CREATE TABLE ' + @TABLENAME + '(' SELECT @SQL = @SQL + '[ID] [int] IDENTITY(1,1) NOT NULL,[FinPeriodNr] [int] NOT NULL,[FinCurrentPeriod] [bit] NULL,[FinStart] [date] NULL,[FinEnd] [date] NULL,[FinStatusOpen] [bit] NOT NULL,[PeriodClosedTS] [smalldatetime] NULL,[PeriodClosedUID] [varchar](3) NULL,)' CONSTRAINT [PK_FinancialPeriod' + @TABLENAME + '_1] PRIMARY KEY CLUSTERED( [FinPeriodNr] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] EXEC(@SQL) SET NOCOUNT ON; END