Hi I have the following stored proc:
However @ProjectQuestionId int . Is used as input here. But I need it in the second insert.
What should I declare it as.
I get the id from the select statement and I need to insert it in the following table:
CREATE TABLE [dbo].[ProjectResponse](
[ProjectRespondentId] [int] NOT NULL,
[ProjectQuestionId] [int] NOT NULL,
[ProjectResponseMention] [int] NULL,
[ProjectResponseContent] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProjectResponseOpenEndContent] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Question is do I declare it as output?
ALTER Procedure [dbo].[prAddProjectResponseandRespondent]
@ProjectRespondentVcId nvarchar(255),
@ProjectRespondentCompletionDate datetime,
@ProjectQuestionId int ,
@ProjectResponseMention int ,
@ProjectResponseContent nvarchar(255) ,
@ProjectResponseOpenEndContent ntext
AS
BEGIN
declare @ProjectRespondentId int
--SET NOCOUNT ON
BEGIN TRAN --- ------------------ Start of transaction
INSERT INTO [ProjectRespondent] (
ProjectRespondentVcId,
ProjectRespondentCompletionDate)
VALUES (
@ProjectRespondentVcId,
@ProjectRespondentCompletionDate)
SELECT @ProjectRespondentId = SCOPE_IDENTITY()
SELECT
ProjectQuestion.ProjectQuestionId,
ProjectResponse.ProjectResponseContent,
ProjectResponse.ProjectResponseMention,
ProjectResponse.ProjectResponseOpenEndContent
FROM ProjectResponse AS ProjectResponse
INNER JOIN ProjectQuestion ON ProjectResponse.ProjectResponseContent = ProjectQuestion.ProjectQuestionName
INSERT INTO [RG_ProjectData].[dbo].[ProjectResponse]
([ProjectRespondentId]
,[ProjectQuestionId]
,[ProjectResponseMention]
,[ProjectResponseContent]
,[ProjectResponseOpenEndContent])
VALUES
(@ProjectRespondentId,
@ProjectQuestionId,
@ProjectResponseMention,
@ProjectResponseContent,
@ProjectResponseOpenEndContent)
COMMIT TRAN ---------------------------- Commit the transaction
RETURN 0
END