Q:

SQL create table stored procedure not setting defaults properly

I am trying to create a table with a stored procedure.  I scripted the table as a CREATE statement and copied the file contents into my procedure, which works great.  Except that when it sets defaults, it sets the first columns default as the whole second line.
Here's the procedure:
CREATE PROCEDURE [dbo].[spCreatetblLocation]
AS
EXEC 
('
 SET ANSI_NULLS ON
 SET QUOTED_IDENTIFIER ON

 CREATE TABLE [dbo].[tblLocation](
  [pkLocationID] [int] IDENTITY(1,1) NOT NULL,
  [fldName] [nvarchar](100) NOT NULL,
  [fldPath] [nvarchar](1000) NOT NULL,
  [fkYearID] [int] NOT NULL,
  CONSTRAINT [PK_LocationID] PRIMARY KEY CLUSTERED 
  (
   [pkLocationID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]

 ALTER TABLE [dbo].[tblLocation] ADD  DEFAULT N'' FOR [fldName]

 ALTER TABLE [dbo].[tblLocation] ADD  DEFAULT N'' FOR [fldPath]
 ')

After execution, column [fldName] has a default value of FOR [fldName] ALTER TABLE [dbo].[tblLocation] ADD  DEFAULT N'' FOR [fldPath]
It makes sense that it's just not recognizing the second quotation mark in (N''), but why not?  And how do I fix it?
UPDATE:
I have learned that the proper way to create tables is through reading and executing sql script files.  This keeps data manipulation and database manipulation seperate, and easier to manage.

A:

If you have an apostrophe within a string in SQL, you need to escape it with an additional apostrophe. Try:
CREATE PROCEDURE [dbo].[spCreatetblLocation]
AS
EXEC 
('
 SET ANSI_NULLS ON
 SET QUOTED_IDENTIFIER ON

 CREATE TABLE [dbo].[tblLocation](
  [pkLocationID] [int] IDENTITY(1,1) NOT NULL,
  [fldName] [nvarchar](100) NOT NULL,
  [fldPath] [nvarchar](1000) NOT NULL,
  [fkYearID] [int] NOT NULL,
  CONSTRAINT [PK_LocationID] PRIMARY KEY CLUSTERED 
  (
   [pkLocationID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]

 ALTER TABLE [dbo].[tblLocation] ADD  DEFAULT N'''' FOR [fldName]

 ALTER TABLE [dbo].[tblLocation] ADD  DEFAULT N'''' FOR [fldPath]
 ')

It's not clear (to me at least) why you're doing it this way though, instead of just:
CREATE PROCEDURE [dbo].[spCreatetblLocation]
AS
 SET ANSI_NULLS ON
 SET QUOTED_IDENTIFIER ON

 CREATE TABLE [dbo].[tblLocation](
  [pkLocationID] [int] IDENTITY(1,1) NOT NULL,
  [fldName] [nvarchar](100) NOT NULL,
  [fldPath] [nvarchar](1000) NOT NULL,
  [fkYearID] [int] NOT NULL,
  CONSTRAINT [PK_LocationID] PRIMARY KEY CLUSTERED 
  (
   [pkLocationID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]

 ALTER TABLE [dbo].[tblLocation] ADD  DEFAULT N'' FOR [fldName]

 ALTER TABLE [dbo].[tblLocation] ADD  DEFAULT N'' FOR [fldPath]

Also not clear why you're creating a table within a procedure, especially without checking whether it already exists first - can you guarantee that this table won't already exist every time your procedure is run?

