24
{System.Data.SqlClient.SqlException: Incorrect syntax near 't'.
Must declare the scalar variable "@ProjectQuestionId".
Unclosed quotation mark after the character string ''.
Must declare the scalar variable "@ProjectCodeId".
Incorrect syntax near 't'.
Must declare the scalar variable "@ProjectQuestionNo".
USE [RG10_Study]
GO
/****** Object: StoredProcedure [dbo].[Rg_sp_CreateProjectProcedures] Script Date: 10/02/2006 11:44:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER 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_CreateProjectDatabase
************************************************** ********************************/
CREATE procedure [dbo].[Rg_sp_CreateProjectProcedures] @nvcDatabaseName nvarchar(255)
as
DECLARE @SQL NVARCHAR(4000)
SET @SQL = N'EXECUTE ' + @nvcDatabaseName + N'.dbo.sp_executesql N''
CREATE PROCEDURE [dbo].[AddProjectQuestionContentFrequency]
AS
SET NOCOUNT ON
INSERT INTO [ProjectQuestionContentFrequency] (
ProjectQuestionId,
ProjectQuestionContent,
ProjectQuestionContentFrequency,
ProjectQuestionContentDateTime)
SELECT
ProjectQuestionId,
ProjectResponseContent,
count (*) ,
GETDATE()
from ProjectResponse
group by ProjectQuestionId, ProjectResponseContent'''
EXECUTE (@SQL)
DECLARE @SQL1 NVARCHAR(4000)
SET @SQL1 = N'EXECUTE ' + @nvcDatabaseName + N'.dbo.sp_executesql N''
CREATE PROCEDURE [dbo].[AddProjectQuestionIdFrequency]
AS
SET NOCOUNT ON
INSERT INTO [ProjectQuestionIdFrequency] (
ProjectQuestionId,
ProjectQuestionFrequency,
ProjectQuestionIdDateTime)
SELECT
ProjectQuestionId,
count (*) ,
GETDATE()
from ProjectResponse
group by ProjectQuestionId'''
EXECUTE (@SQL1)
DECLARE @SQL2 NVARCHAR(4000)
SET @SQL2 = N'EXECUTE ' + @nvcDatabaseName + N'.dbo.sp_executesql N''
CREATE Procedure [dbo].[prAddProjectCode]
@ProjectQuestionId int,
@ProjectCode nvarchar(255),
@ProjectCodeLabelId int,
@ProjectCodeIsOpen bit,
@ProjectCodeIsActive bit,
@ProjectCodeId int OUTPUT
AS
-- Improve performance - don''t report number of rows
SET NOCOUNT ON
INSERT INTO [ProjectCode] (
ProjectQuestionId,
ProjectCode,
ProjectCodeLabelId,
ProjectCodeIsOpen,
ProjectCodeIsActive)
VALUES (
@ProjectQuestionId,
@ProjectCode,
@ProjectCodeLabelId,
@ProjectCodeIsOpen,
@ProjectCodeIsActive)
SELECT @ProjectCodeId = SCOPE_IDENTITY()
'''
EXECUTE (@SQL2)
DECLARE @SQL3 NVARCHAR(4000)
SET @SQL3 = N'EXECUTE ' + @nvcDatabaseName + N'.dbo.sp_executesql N''
CREATE Procedure [dbo].[prAddProjectQuestion]
(
@ProjectQuestionNo int,
@ProjectQuestionType nvarchar(255),
@ProjectQuestionName nvarchar(255),
@ProjectQuestionLastModifiedDate datetime,
@ProjectQuestionDateFormat nvarchar(255),
@ProjectQuestionShortLabelId int,
@ProjectQuestionLongLabelId int,
@ProjectQuestionMessageId int,
@ProjectQuestionMinAnswer tinyint,
@ProjectQuestionMaxAnswer tinyint,
@ProjectQuestionCodeWidth int,
@ProjectQuestionHasOpenEnd bit,
@ProjectQuestionLBoundary nvarchar(255),
@ProjectQuestionUBounday nvarchar(255),
@ProjectQuestionMaxtrixNo int,
@ProjectQuestionMatrixLength int,
@ProjectQuestionIsActive bit,
@ProjectQuestionIsCalculated bit,
@ProjectQuestionId int OUTPUT
)
AS
-- Improve performance - don''t report number of rows
SET NOCOUNT ON
INSERT INTO dbo.ProjectQuestion
([ProjectQuestionNo]
,[ProjectQuestionType]
,[ProjectQuestionName]
,[ProjectQuestionLastModifiedDate]
,[ProjectQuestionDateFormat]
,[ProjectQuestionShortLabelId]
,[ProjectQuestionLongLabelId]
,[ProjectQuestionMessageId]
,[ProjectQuestionMinAnswer]
,[ProjectQuestionMaxAnswer]
,[ProjectQuestionCodeWidth]
,[ProjectQuestionHasOpenEnd]
,[ProjectQuestionLBoundary]
,[ProjectQuestionUBounday]
,[ProjectQuestionMaxtrixNo]
,[ProjectQuestionMatrixLength]
,[ProjectQuestionIsActive]
,[ProjectQuestionIsCalculated])
VALUES
(
@ProjectQuestionNo,
@ProjectQuestionType,
@ProjectQuestionName,
@ProjectQuestionLastModifiedDate,
@ProjectQuestionDateFormat,
@ProjectQuestionShortLabelId,
@ProjectQuestionLongLabelId,
@ProjectQuestionMessageId,
@ProjectQuestionMinAnswer,
@ProjectQuestionMaxAnswer,
@ProjectQuestionCodeWidth,
@ProjectQuestionHasOpenEnd,
@ProjectQuestionLBoundary,
@ProjectQuestionUBounday,
@ProjectQuestionMaxtrixNo,
@ProjectQuestionMatrixLength,
@ProjectQuestionIsActive,
@ProjectQuestionIsCalculated
)
SELECT @ProjectQuestionId = SCOPE_IDENTITY()
'''
EXECUTE (@SQL3)
DECLARE @SQL4 NVARCHAR(4000)
SET @SQL4 = N'EXECUTE ' + @nvcDatabaseName + N'.dbo.sp_executesql N''
CREATE Procedure [dbo].[prAddProjectResponseandRespondent]
--- five inputs
@ProjectRespondentVcId nvarchar(255), --- this is used for first insert
@ProjectRespondentCompletionDate datetime, --- this is used for first insert
@ProjectQuestionId int,
@ProjectResponseMention int , --- this is used for second insert
@ProjectResponseContent nvarchar(255) , --- this is used for second insert
@ProjectResponseOpenEndContent ntext --- this is used for second insert
AS
BEGIN
declare @ProjectRespondentId int
SET NOCOUNT ON
BEGIN TRAN
INSERT INTO [ProjectRespondent] (
ProjectRespondentVcId,
ProjectRespondentCompletionDate)
VALUES (
@ProjectRespondentVcId,
@ProjectRespondentCompletionDate)
SELECT @ProjectRespondentId = SCOPE_IDENTITY()
INSERT INTO [RG_ProjectData].[dbo].[ProjectResponse]
([ProjectRespondentId]
,[ProjectQuestionId]
,[ProjectResponseMention]
,[ProjectResponseContent]
,[ProjectResponseOpenEndContent])
VALUES
(@ProjectRespondentId,
@ProjectQuestionId,
@ProjectResponseMention,
@ProjectResponseContent,
@ProjectResponseOpenEndContent)
COMMIT TRAN ---------------------------- Commit the transaction
'''
EXECUTE (@SQL4)
