CODEWITHSUNDEEP
c#sql-server-2008stored-procedures
K.Z
K.Z

Reputation: 5075

2nd Query in SQL Stored Procedure giving me Null error

I need to run three Insert queries in SQL Server 2008 via stored procedure and expected three OUTPUT values to read in C#. In following Stored Procedure, my first query only runs and remain I am getting Null error

Cannot insert the value NULL into column 'AssessmentElectronicSignatureID'

 USE [myDB]
    GO
    /****** Object:  StoredProcedure [dbo]. [p_assessment_dfn_statementAnswer_insert]    Script Date: 10/05/2015 13:24:22 ******/
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO

    ALTER PROCEDURE [dbo].[p_assessment_dfn_statementAnswer_insert]
                  @StatementID AS bigint,
                  @StaffID AS int,
                  @AssessmentID As bigint,
                  @StatementText AS nvarchar(MAX), 
                  @StatementDate AS Date,
                  @StatementAnswerID AS bigint OUTPUT,
                  --
                  @SignatureCheck AS bit,
                  @SignatureDate AS Date,
                  @ElectronicSignatureID AS bigint OUTPUT,
                  --
                  @AssessmentElectronicSignatureID AS bigint OUTPUT
    AS
    SET NOCOUNT ON 
    SET XACT_ABORT ON 

    -- local variables
     DECLARE @l_object    AS SYSNAME = OBJECT_NAME(@@PROCID)
           ,@l_error_msg AS NVARCHAR(2000)   


    BEGIN TRY

      BEGIN TRAN

         INSERT INTO [adb_TestDb].[dbo].[Assessment_Statement_Answer]
           ([StatementID],[StaffID],[AssessmentID],[StatementText],[Date])
     VALUES (@StatementID, @StaffID, @AssessmentID, @StatementText, @StatementDate)


     SELECT @StatementAnswerID = SCOPE_IDENTITY();

     IF(@StatementAnswerID>0)
     BEGIN
        INSERT INTO [adb_TestDb].[dbo].[Assessment_ElectronicSignature]([AssessmentID],[ElectronicSignatureCheck],[SignatureDateAndTime])
        VALUES (@AssessmentID, @SignatureCheck,@SignatureDate)

        SELECT @ElectronicSignatureID = SCOPE_IDENTITY();
     END


     IF(@ElectronicSignatureID>0)
      BEGIN
        INSERT INTO [adb_TestDb].[dbo].[AssessorSignature]([AssessmentElectronicSignatureID],[StatementAnswerID],[AssessorID])
        VALUES(@ElectronicSignatureID, @StatementAnswerID, @AssessmentID)

        SELECT @AssessmentElectronicSignatureID = SCOPE_IDENTITY();
       END
    COMMIT TRAN 

     --RETURN SCOPE_IDENTITY();

    END TRY

    BEGIN CATCH

    -- rollback any open/uncomitted transactions
    IF XACT_STATE() IN ( -1, 1) ROLLBACK TRANSACTION            

    -- return an error containing the object, error number and error description
    SELECT @l_error_msg = 'Error number : ' + CAST(ERROR_NUMBER()AS VARCHAR) + '. ' + ERROR_MESSAGE()          
    RAISERROR (@l_error_msg,16,1)

    END CATCH

Upvotes: 0

Views: 47

Answers (1)

Abdul Saleem
Abdul Saleem

Reputation: 10622

There is a known bug with SCOPE_IDENTITY(); Occurs occasionally when triggers are used. Google for the occurances if you want to dig deep. Or...

Try changing things like

SELECT @StatementAnswerID = SCOPE_IDENTITY();

to

SELECT @StatementAnswerID = @@IDENTITY;

Or

SET @StatementAnswerID = @@IDENTITY;

and other similar assignments. I think the problem here is causing because of being the @StatementAnswerID as Null.

Upvotes: 2

Related Questions