Problem with stored proc.

[macupryk]

{System.Data.Sq lClient.SqlExce ption: Incorrect syntax near 't'.
Must declare the scalar variable "@ProjectQuesti onId".
Unclosed quotation mark after the character string ''.
Must declare the scalar variable "@ProjectCodeId ".
Incorrect syntax near 't'.
Must declare the scalar variable "@ProjectQuesti onNo".

USE [RG10_Study]

GO

/****** Object: StoredProcedure [dbo].[Rg_sp_CreatePro jectProcedures] Script Date: 10/02/2006 11:44:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFI ER ON

GO





/*************** *************** *************** *************** *************** *****

Creation Date Sept. 20, 2006

By Mathieu Cupryk

Mod Date

By

Parameters @nvcDwName Input Full datawarehouse name i-f null exits

Returns nothing

Description Create tables in database RG10 which was just created by

RG10_sp_CreateP rojectDatabase

*************** *************** *************** *************** *************** *******/





CREATE procedure [dbo].[Rg_sp_CreatePro jectProcedures] @nvcDatabaseNam e nvarchar(255)

as

DECLARE @SQL NVARCHAR(4000)

SET @SQL = N'EXECUTE ' + @nvcDatabaseNam e + N'.dbo.sp_execu tesql N''

CREATE PROCEDURE [dbo].[AddProjectQuest ionContentFrequ ency]

AS

SET NOCOUNT ON

INSERT INTO [ProjectQuestion ContentFrequenc y] (

ProjectQuestion Id,

ProjectQuestion Content,

ProjectQuestion ContentFrequenc y,

ProjectQuestion ContentDateTime )

SELECT

ProjectQuestion Id,

ProjectResponse Content,

count (*) ,

GETDATE()

from ProjectResponse

group by ProjectQuestion Id, ProjectResponse Content'''

EXECUTE (@SQL)


DECLARE @SQL1 NVARCHAR(4000)

SET @SQL1 = N'EXECUTE ' + @nvcDatabaseNam e + N'.dbo.sp_execu tesql N''

CREATE PROCEDURE [dbo].[AddProjectQuest ionIdFrequency]

AS

SET NOCOUNT ON

INSERT INTO [ProjectQuestion IdFrequency] (

ProjectQuestion Id,

ProjectQuestion Frequency,

ProjectQuestion IdDateTime)

SELECT

ProjectQuestion Id,

count (*) ,

GETDATE()

from ProjectResponse

group by ProjectQuestion Id'''

EXECUTE (@SQL1)

DECLARE @SQL2 NVARCHAR(4000)

SET @SQL2 = N'EXECUTE ' + @nvcDatabaseNam e + N'.dbo.sp_execu tesql N''

CREATE Procedure [dbo].[prAddProjectCod e]

@ProjectQuestio nId int,

@ProjectCode nvarchar(255),

@ProjectCodeLab elId int,

@ProjectCodeIsO pen bit,

@ProjectCodeIsA ctive bit,

@ProjectCodeId int OUTPUT

AS

-- Improve performance - don''t report number of rows

SET NOCOUNT ON

INSERT INTO [ProjectCode] (

ProjectQuestion Id,

ProjectCode,

ProjectCodeLabe lId,

ProjectCodeIsOp en,

ProjectCodeIsAc tive)

VALUES (

@ProjectQuestio nId,

@ProjectCode,

@ProjectCodeLab elId,

@ProjectCodeIsO pen,

@ProjectCodeIsA ctive)

SELECT @ProjectCodeId = SCOPE_IDENTITY( )

'''

EXECUTE (@SQL2)

DECLARE @SQL3 NVARCHAR(4000)

SET @SQL3 = N'EXECUTE ' + @nvcDatabaseNam e + N'.dbo.sp_execu tesql N''

CREATE Procedure [dbo].[prAddProjectQue stion]

(

@ProjectQuestio nNo int,

@ProjectQuestio nType nvarchar(255),

@ProjectQuestio nName nvarchar(255),

@ProjectQuestio nLastModifiedDa te datetime,

@ProjectQuestio nDateFormat nvarchar(255),

@ProjectQuestio nShortLabelId int,

@ProjectQuestio nLongLabelId int,

@ProjectQuestio nMessageId int,

@ProjectQuestio nMinAnswer tinyint,

@ProjectQuestio nMaxAnswer tinyint,

@ProjectQuestio nCodeWidth int,

@ProjectQuestio nHasOpenEnd bit,

@ProjectQuestio nLBoundary nvarchar(255),

@ProjectQuestio nUBounday nvarchar(255),

@ProjectQuestio nMaxtrixNo int,

@ProjectQuestio nMatrixLength int,

@ProjectQuestio nIsActive bit,

@ProjectQuestio nIsCalculated bit,

@ProjectQuestio nId int OUTPUT

)

AS

-- Improve performance - don''t report number of rows

SET NOCOUNT ON

INSERT INTO dbo.ProjectQues tion

([ProjectQuestion No]

,[ProjectQuestion Type]

,[ProjectQuestion Name]

,[ProjectQuestion LastModifiedDat e]

,[ProjectQuestion DateFormat]

,[ProjectQuestion ShortLabelId]

,[ProjectQuestion LongLabelId]

,[ProjectQuestion MessageId]

,[ProjectQuestion MinAnswer]

,[ProjectQuestion MaxAnswer]

,[ProjectQuestion CodeWidth]

,[ProjectQuestion HasOpenEnd]

,[ProjectQuestion LBoundary]

,[ProjectQuestion UBounday]

,[ProjectQuestion MaxtrixNo]

,[ProjectQuestion MatrixLength]

,[ProjectQuestion IsActive]

,[ProjectQuestion IsCalculated])

VALUES

(

@ProjectQuestio nNo,

@ProjectQuestio nType,

@ProjectQuestio nName,

@ProjectQuestio nLastModifiedDa te,

@ProjectQuestio nDateFormat,

@ProjectQuestio nShortLabelId,

@ProjectQuestio nLongLabelId,

@ProjectQuestio nMessageId,

@ProjectQuestio nMinAnswer,

@ProjectQuestio nMaxAnswer,

@ProjectQuestio nCodeWidth,

@ProjectQuestio nHasOpenEnd,

@ProjectQuestio nLBoundary,

@ProjectQuestio nUBounday,

@ProjectQuestio nMaxtrixNo,

@ProjectQuestio nMatrixLength,

@ProjectQuestio nIsActive,

@ProjectQuestio nIsCalculated

)



SELECT @ProjectQuestio nId = SCOPE_IDENTITY( )

'''

EXECUTE (@SQL3)

DECLARE @SQL4 NVARCHAR(4000)

SET @SQL4 = N'EXECUTE ' + @nvcDatabaseNam e + N'.dbo.sp_execu tesql N''

CREATE Procedure [dbo].[prAddProjectRes ponseandRespond ent]

--- five inputs

@ProjectRespond entVcId nvarchar(255), --- this is used for first insert

@ProjectRespond entCompletionDa te datetime, --- this is used for first insert

@ProjectQuestio nId int,

@ProjectRespons eMention int , --- this is used for second insert

@ProjectRespons eContent nvarchar(255) , --- this is used for second insert

@ProjectRespons eOpenEndContent ntext --- this is used for second insert

AS

BEGIN

declare @ProjectRespond entId int

SET NOCOUNT ON

BEGIN TRAN

INSERT INTO [ProjectResponde nt] (

ProjectResponde ntVcId,

ProjectResponde ntCompletionDat e)

VALUES (

@ProjectRespond entVcId,

@ProjectRespond entCompletionDa te)


SELECT @ProjectRespond entId = SCOPE_IDENTITY( )



INSERT INTO [RG_ProjectData].[dbo].[ProjectResponse]

([ProjectResponde ntId]

,[ProjectQuestion Id]

,[ProjectResponse Mention]

,[ProjectResponse Content]

,[ProjectResponse OpenEndContent])

VALUES

(@ProjectRespon dentId,

@ProjectQuestio nId,

@ProjectRespons eMention,

@ProjectRespons eContent,

@ProjectRespons eOpenEndContent )

COMMIT TRAN ---------------------------- Commit the transaction

'''

EXECUTE (@SQL4)