Stored Procedure error

[supersal666]

Hi everyone, I have a stored procedure which populates some tables in a database. The error I get is

Procedure or function 'sAddCompletedQuestionnaire' expects parameter '@CompletedQuestionnaireResponseID', which was not supplied.[/color red]

the problem seems to be when trying to populate an identity column. The part in red is where it seems to give the error.

USE [MarketingQuestionnaire]
GO
/****** Object:  StoredProcedure [dbo].[sAddCompletedQuestionnaire]    Script Date: 11/06/2007 09:37:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sAddCompletedQuestionnaire]
	(
	 @CompletedQuestionnaireID varchar(50) OUTPUT
	,@AuditDateID int
	,@CompletedQuestionnaireResponseID int
	)
AS

--***************************************************************************--
--************ Insert into dbo.ApplicationDate ******************************--
--***************************************************************************--

INSERT INTO dbo.ApplicationDate
	(
		AuditDate
	) 
VALUES 
	(
		GetDate()
	) 
SELECT @AuditDateID = @@IDENTITY;

--***************************************************************************--
--************ Insert into dbo.CompletedQuestionnaire ***********************--
--***************************************************************************--

INSERT INTO dbo.CompletedQuestionnaire
	(
		CompletedQuestionnaireID
		,AuditDateID
	)
SELECT 
		Q1 
		,@AuditDateID 
FROM 
		dbo.Satisfaction1

--***************************************************************************--
--************ Insert into dbo.CompletedQuestionnaireResponse ***************--
--***************************************************************************--

[COLOR=red]SELECT @CompletedQuestionnaireResponseID = @@IDENTITY[/color]

INSERT INTO dbo.CompletedQuestionnaireResponse
	(
		 CompletedQuestionnaireID 
		,CompletedQuestionnaireResponseID
	)
		SELECT  @CompletedQuestionnaireID 
		,@CompletedQuestionnaireResponseID

GO

DECLARE @CompletedQuestionnaireID bigint 
DECLARE @AuditDateID int
DECLARE @CompletedQuestionnaireResponseID  int
SELECT @AuditDateID
EXEC sAddCompletedQuestionnaire @CompletedQuestionnaireID OUTPUT ,@AuditDateID
SELECT @CompletedQuestionnaireID 

--SELECT @CompletedQuestionnaireResponseID

 

[bborissov]

WHY you add @CompletedQuestionnaireResponseID as parameter if you set its value inside the SP?
Also NEVER, NEVER use @@IDENTITY. That will give you a wrong result if you have a trigger in some table which insert a record in other table that have identity column. Use SCOPE_IDENTITY() instead:

USE [MarketingQuestionnaire]
GO
/****** Object:  StoredProcedure [dbo].[sAddCompletedQuestionnaire]    Script Date: 11/06/2007 09:37:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sAddCompletedQuestionnaire]
    (
     @CompletedQuestionnaireID varchar(50) OUTPUT
    ,@AuditDateID int
    ,@CompletedQuestionnaireResponseID int
    )
AS

--***************************************************************************--
--************ Insert into dbo.ApplicationDate ******************************--
--***************************************************************************--

INSERT INTO dbo.ApplicationDate
    (
        AuditDate
    )
VALUES
    (
        GetDate()
    )

SELECT @AuditDateID = SCOPE_IDENTITY()

--***************************************************************************--
--************ Insert into dbo.CompletedQuestionnaire ***********************--
--***************************************************************************--

INSERT INTO dbo.CompletedQuestionnaire
    (
        CompletedQuestionnaireID
        ,AuditDateID
    )
SELECT
        Q1
        ,@AuditDateID
FROM
        dbo.Satisfaction1

--***************************************************************************--
--************ Insert into dbo.CompletedQuestionnaireResponse ***************--
--***************************************************************************--
-- ???????????????????????
-- Why you need that as a parameter????????
-- ???????????????????????
SELECT @CompletedQuestionnaireResponseID = SCOPE_IDENTITY()

INSERT INTO dbo.CompletedQuestionnaireResponse
    (
         CompletedQuestionnaireID
        ,CompletedQuestionnaireResponseID
    )
        SELECT  @CompletedQuestionnaireID
        ,@CompletedQuestionnaireResponseID

GO

DECLARE @CompletedQuestionnaireID bigint
DECLARE @AuditDateID int
DECLARE @CompletedQuestionnaireResponseID  int
SELECT @AuditDateID
EXEC sAddCompletedQuestionnaire @CompletedQuestionnaireID OUTPUT ,@AuditDateID
SELECT @CompletedQuestionnaireID

--SELECT @CompletedQuestionnaireResponseID

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[TysonLPrice]

Are you expecting @CompletedQuestionnaireResponseID to be output? Otherwise don't you have to feed it to the SP?

Also you shouldn't be using @@Identity. Look into SCOPE_IDENTITY.

 

[supersal666]

Hi everyone, thanks for the feedback, well I haven't written any stored procedures before, I am trying to start to use them rather than doing all this in my code.

I don't need CompletedQuestionnaireResponseID as a parameter, I basically want this to be created and just to get the value back so that I can insert it into another table. I think thats why I get the 'expects parameter' value isn't it? Is it because it is expecting a value for CompletedQuestionnaireResponseID?

 

[bborissov]

Yes, Remove it from Parameter list and declare it as a local variable in Procedure body.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP