Problem with creating a table using a stored procedure [modified]

TheComputerMan

Hi, I searched the boards and found how to do this, but I have come up against a problem that I cannot seem to resolve. This is the code to create the stored procedure: (It was create first but I have been playing with it)

ALTER PROCEDURE [dbo].[sproc_CreateMarriageTable]
-- Add the parameters for the stored procedure here
@Surname varchar(50)
AS
BEGIN
DECLARE @SQLStatement varchar(1500)
SET @SQLStatement = 'CREATE TABLE [' + @Surname + ']
([AutoID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname +'_FName] DEFAULT (''''),
[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_SName] DEFAULT (''''),
[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_TName] DEFAULT (''''),
[MF] [nvarchar](1) NOT NULL CONSTRAINT [DF_' + @Surname + '_MF] DEFAULT (''M''),
[Year] [smallint] NULL CONSTRAINT [DF_' + @Surname + '_Year] DEFAULT ((0)),
[Quarter] [smallint] NULL CONSTRAINT [DF_' + @Surname + '_Qtr] DEFAULT ((1)),
[PersonMarried] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_PerMar] DEFAULT (''''),
[BirthDate] [smalldatetime] NULL,
[Volume] [nvarchar](6) NULL CONSTRAINT [DF_' + @Surname + '_Vol] DEFAULT (''''),
[PageNo] [nvarchar](6) NULL CONSTRAINT [DF_' + @Surname + '_PNo] DEFAULT (''''),
[RegDistrictCode] [int] NOT NULL CONSTRAINT [DF_' + @Surname + '_RegDistCode] DEFAULT ((0)), CONSTRAINT [PK_' +@Surname + ']
PRIMARY KEY CLUSTERED ([AutoID] 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 @SQLStatement

END

This parses and compiles without any errors. Then I tried to execute the procedure:

USE [BMD2005]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[sproc_CreateMarriageTable]
@Surname = N'M_VAISEY'

SELECT 'Return Value' = @return_value

GO

It fails with the message: Msg 203, Level 16, State 2, Procedure sproc_CreateMarriageTable, Line 29 The name 'CREATE TABLE [M_VAISEY] ([AutoID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_FName] DEFAULT (''), [SecondName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_SName] DEFAULT (''), [ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_TName] DEFAULT (''), [MF] [nvarchar](1) NOT NULL CONSTRAINT [DF_M_VAISEY_MF] DEFAULT ('M'), [Year] [smallint] NULL CONSTRAINT [DF_M_VAISEY_Year] DEFAULT ((0)), [Quarter] [smalli

TheComputerMan

OK, I solved it!

DECLARE @SQLStatement varchar(1024)
DECLARE @SQLStatement2 varchar(1024)
SET @SQLStatement = 'CREATE TABLE \[' + @Surname + '\]
(\[AutoID\] \[int\] IDENTITY(1,1) NOT NULL,\[FirstName\] \[nvarchar\](50) NULL CONSTRAINT \[DF\_' + @Surname +'\_FName\]  DEFAULT (''''), 
\[SecondName\] \[nvarchar\](50) NULL CONSTRAINT \[DF\_' + @Surname + '\_SName\]  DEFAULT (''''), 
\[ThirdName\] \[nvarchar\](50) NULL CONSTRAINT \[DF\_' + @Surname + '\_TName\]  DEFAULT (''''), 
\[MF\] \[nvarchar\](1) NOT NULL CONSTRAINT \[DF\_' + @Surname + '\_MF\]  DEFAULT (''M''), 
\[Year\] \[smallint\] NULL CONSTRAINT \[DF\_' + @Surname + '\_Year\]  DEFAULT ((0)), 
\[Quarter\] \[smallint\] NULL CONSTRAINT \[DF\_' + @Surname + '\_Qtr\]  DEFAULT ((1)), 
\[PersonMarried\] \[nvarchar\](50) NULL CONSTRAINT \[DF\_' + @Surname + '\_PerMar\]  DEFAULT (''''), '
SET @SQLStatement2 = '\[BirthDate\] \[smalldatetime\] NULL, 
\[Volume\] \[nvarchar\](6) NULL CONSTRAINT \[DF\_' + @Surname + '\_Vol\]  DEFAULT (''''), 
\[PageNo\] \[nvarchar\](6) NULL CONSTRAINT \[DF\_' + @Surname + '\_PNo\]  DEFAULT (''''), 
\[RegDistrictCode\] \[int\] NOT NULL CONSTRAINT \[DF\_' + @Surname + '\_RegDistCode\]  DEFAULT ((0)), CONSTRAINT \[PK\_' +@Surname + '\] 
PRIMARY KEY CLUSTERED (\[AutoID\] 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 (@SQLStatement + @SQLStatement2)

Kevin Horgan

Hi, It looks like the length is the issue. I guess you might have some tab characters in the code for formatting perhaps, not sure. Anyway, I just fixed a similar problem in a Sybase IQ procedure and just removed all the white space. So for example... DECLARE @SQLStatement varchar(1500) SET @SQLStatement = 'CREATE TABLE [' + @Surname + '] ([AutoID] [int] IDENTITY(1,1) NOT NULL,' + '[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_FName] DEFAULT (''''), ' + '[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_SName] DEFAULT (''''), ' + '[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname ... etc ... etc ... etc + ' STATISTICS_NORECOMPUTE = OFF, ' + ' IGNORE_DUP_KEY = OFF, ' + ' ALLOW_ROW_LOCKS = ON, ' + ' ALLOW_PAGE_LOCKS = ON)' + ' ON [PRIMARY]) ' + ' ON [PRIMARY]' EXEC @SQLStatement I hope that helps. Good luck :) , Kevin

TheComputerMan

Took me a second or two to work out what you were saying. *david scratches head* Ah! The light dawned - concatenation :) Yes of course the wrapping or rather formatting may be adding spurious characters. Strange thing is that it worked when I split the line into two. Perhaps I just hit the right spot by luck rather than by judgement! :^) I will give it a try. Thanks David

TheComputerMan

Worked a treat. Thanks again. :) :)