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

Answer 1

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.