0

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

1 Answer 1

5

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:

8
  • +1 for all the little tips on your code (and because it fixed op's problem)
    – Lamak
    Apr 4, 2013 at 15:17
  • You make it look so simple! And best of all it works perfectly! Thank you very much Aaron! Help much appreciated! ^_^ (p.s i will be sure to check out the links to further my understanding of SQL!)
    – Justin
    Apr 4, 2013 at 15:29
  • Could go further and use sysname for the parameter, and QUOTENAME when injecting the value into the string. The latter is obviously more important.
    – Jon Seigel
    Apr 4, 2013 at 17:07
  • @Jon damn dude, I'm usually meticulous about that stuff Apr 4, 2013 at 17:11
  • A second pair of eyes never hurts. :) What do you usually do for the other case of injecting the table name into the constraint name? I can't say I've ever had to do it like that before.
    – Jon Seigel
    Apr 4, 2013 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.