Sql Scope_Identity() return Null?

Question

I am facing this issue that my stored proc always return NULL though i have set my @output variable as well.I want to get last inserted scope Id from the table.

can someone help me where i have got wrong?

   ALTER PROC spAddOrUpdateMember

     @pMemberId INT =  0 ,
     @pFirstName VARCHAR(50) = 'aa',
     @pLastName VARCHAR(50)='aa' ,
     @pMemberCode VARCHAR(15) = '12312',
     @pDOB DATE = '03/10/2019',
     @pGrade INT = 2 , 
     @pCNIC VARCHAR(14) = '3423434',
     @pFatherName VARCHAR(50) = 'asdasd' , 
     @pCurrentAddress VARCHAR(MAX) = 'asds' , 
     @pPermanentAddress VARCHAR(MAX) = 'fgdf',
     @pEmploymentAddress VARCHAR(MAX) = 'ytuyu' ,
     @pNationality INT =2
     @output int = 0 output    

AS

    BEGIN   
            IF @pMemberId > 0
                BEGIN
                        ---UPDATE ME
                            UPDATE [dbo].[QC_Member_Profile]
                           SET 
                              [FirstName] = @pFirstName
                              ,[LastName] = @pLastName
                              ,[DOB] = @pDOB
                              ,[CNIC] = @pCNIC
                              ,[FatherName] = @pFatherName
                              ,[CurrentAddress] = @pCurrentAddress
                              ,[PermanentAddress] = @pPermanentAddress
                              ,[Nationality] = @pNationality
                              ,[MemberTypeId] =@pMemberTypeId

                         WHERE MemberId  = @pMemberId

                END
            ELSE 
                BEGIN
                    ---INSERT ME
                    INSERT INTO QC_Member_Profile VALUES(
                                                         dbo.PIdentityKey(0),
                                                         @pFirstName,
                                                         @pLastName,
                                                         @pDOB,
                                                         @pCNIC,
                                                         @pFatherName,
                                                         @pCurrentAddress,
                                                         @pPermanentAddress,
                                                         @pNationality,
                                                        )

                                set @output  = SCOPE_IDENTITY(); 
                                SELECT @output  =  SCOPE_IDENTITY();
                                select @output
                END


    END

Don't use `SCOPE_IDENTITY()` at all. us the `OUTPUT` clause. — Gordon Linoff, Mar 19 '19 at 11:17
But @output is output variable which need to set as Scope_Ident? — naveed ahmed, Mar 19 '19 at 11:19
Not the `OUTPUT` parameter type, naveed, the `OUTPUT` clause: [OUTPUT Clause (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017). If you need to get pass that back via the `OUTPUT` parameter type use the `OUTPUT` clause the put the values in a table variable and then set the values of your parameters from there. — Thom A, Mar 19 '19 at 11:20
i have never use Output as Clause .. can you update my code with your answer ?? — naveed ahmed, Mar 19 '19 at 11:27
See https://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value — shahkalpesh, Mar 19 '19 at 11:29

Thom A · Accepted Answer · 2019-03-19T11:43:34.137

I've guessed the name of your ID column, however, this should work. You'll need to amend the name of your ID column if it isn't called MemberID or if it doesn't have the data type int:

ALTER PROC spAddOrUpdateMember @pMemberId int = 0,
                               @pFirstName varchar(50) = 'aa',
                               @pLastName varchar(50) = 'aa',
                               @pMemberCode varchar(15) = '12312',
                               @pDOB date = '03/10/2019',
                               @pGrade int = 2,
                               @pCNIC varchar(14) = '3423434',
                               @pFatherName varchar(50) = 'asdasd',
                               @pCurrentAddress varchar(MAX) = 'asds',
                               @pPermanentAddress varchar(MAX) = 'fgdf',
                               @pEmploymentAddress varchar(MAX) = 'ytuyu',
                               @pNationality int = 2,
                               @output int = 0 OUTPUT
AS
BEGIN
    IF @pMemberId > 0
    BEGIN

        UPDATE [dbo].[QC_Member_Profile]
        SET [FirstName] = @pFirstName,
            [LastName] = @pLastName,
            [DOB] = @pDOB,
            [CNIC] = @pCNIC,
            [FatherName] = @pFatherName,
            [CurrentAddress] = @pCurrentAddress,
            [PermanentAddress] = @pPermanentAddress,
            [Nationality] = @pNationality,
            [MemberTypeId] = @pMemberTypeId
        WHERE MemberId = @pMemberId;

    END;
    ELSE
    BEGIN

        DECLARE @ins table (OutputID int);
        INSERT INTO QC_Member_Profile
        OUTPUT Inserted.MemberID --guessed name
        INTO @Ins
        VALUES (dbo.PIdentityKey(0), @pFirstName, @pLastName, @pDOB, @pCNIC, @pFatherName, @pCurrentAddress, @pPermanentAddress, @pNationality);

        SELECT @output = OutputID 
        FROM @ins;
        SELECT @Output;
    END;


END;

I've also fixed the syntax errors that were in your original question.

This solved my issue :) Thanks.. but whats wrong with SCOPE_IDENTITY ? — naveed ahmed, Mar 19 '19 at 11:38
`SCOPE_IDENTITY` will be affected by things like triggers, other inserts in the same session, can only cater for one row, etc, etc. @naveedahmed . The fact that you were getting the value `NULL` also shows that there's something happening "under the hood" that is changing the value of `SCOPE_IDENTITY` before your `SET @output = SCOPE_IDENTITY();` and `SELECT @output = SCOPE_IDENTITY();` statements (not sure why you're setting it twice). The OUTPUT Clause doesn't suffer these problems/features. — Thom A, Mar 19 '19 at 11:42

Sql Scope_Identity() return Null?

1 Answers1