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