Stored Procedure Create Table from Variable with Variable Constraint

Question

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

Answer 1

Your logic seems fine, you are getting a syntax error because you aren't delimiting your strings correctly.

[PeriodClosedUID] [varchar](3) NULL,)' <-- problem

Also it's very helpful to be liberal with carriage returns and indenting, so problems like this aren't hidden 4 screens over and only accessible via horizontal scrollbar.

CREATE PROCEDURE dbo.procFinancialPeriodTable -- always use schema prefix
    @TABLENAME SYSNAME
AS
BEGIN -- use body wrappers around whole body
  SET NOCOUNT ON; -- put this at the beginning - no point in setting it at the end

  DECLARE @SQL NVARCHAR(MAX); -- dynamic SQL should always be Unicode

  SELECT @SQL = 'CREATE TABLE dbo.' + QUOTENAME(@TABLENAME) + '('
    + '[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]));'; -- all those options you specified were verbose defaults

  EXEC sp_executesql @sql; -- instead of EXEC(@sql)
END
GO

Some links:

• My Stored Procedure "Best Practices" checklist
• Bad habits to kick : Avoiding the schema prefix
• Bad habits to kick : Using EXEC() instead of sp_executesql