Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have managed use a store 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)
    --GO
    SET NOCOUNT ON;
    END
share|improve this question
add comment

1 Answer 1

up vote 5 down vote accepted

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

share|improve this answer
    
+1 for all the little tips on your code (and because it fixed op's problem) –  Lamak Apr 4 '13 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!) –  cocojay Apr 4 '13 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 '13 at 17:07
    
@Jon damn dude, I'm usually meticulous about that stuff –  Aaron Bertrand Apr 4 '13 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 '13 at 17:16
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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