Procedure output parameter always returning 0 in sql

Question

this.status = Convert.ToInt32(((this.db.GetParameterValue(com, "Status") == DBNull.Value) ? 0 : this.db.GetParameterValue(com, "Status")));

The Status is an output parameter from the SQL Server stored procedure but it always returning 0.

Stored procedure is:

CREATE procedure CandidatesloginMaster_CheckSecurityCredential
    @CandidateID int output,
    @Username varchar(25),
    @Password varchar(25),
    @Key1 varchar(25),
    @key2 varchar(25),
    @Status int output
AS
    set NOCOUNT ON

    BEGIN 
    BEGIN TRY       
        IF(@Status = 0)
        BEGIN
            IF exists(
                    select Username,Key1,key2
                    from CandidatesloginMaster
                    where Username = @Username     AND
                          Key1 = @Key1 AND
                          Key2 = @key2
                 )
            BEGIN
                SET @Status = 1
                SET @CandidateID = (SELECT CandidateID 
                                    from CandidatesloginMaster
                                    where Username = @Username     AND
                                    Key1 = @Key1 AND
                                    Key2 = @key2)           
            END 
            ELSE 
            BEGIN
                SET @Status = 0
                SET @CandidateID = 0
            END 
        END
        ELSE IF(@Status = 1)
        BEGIN
            UPDATE CandidatesloginMaster
            SET Userpassword = @Password
            where CandidateID =@CandidateID AND
                  Key1 = @Key1 AND
                  Key2 = @Key2  
        END 
        print @Status
END TRY
BEGIN CATCH
SELECT  ERROR_NUMBER() AS ERROR_NO,
        ERROR_MESSAGE() AS ERROR_MSG,
        ERROR_LINE() AS ERROR_LINE_NO
    END CATCH
END

I running procedure using directly in SQL Server then it print status values as 1:

EXEC CandidatesloginMaster_CheckSecurityCredential 
     1, 'User', 'pwd', 'User123', 'User123', 0

But with the same value through code behind it always returns 0 as status (output parameter).

Answer 1

You defined the first and last parameters as OUT parameters. That means any value stored into those parameters within the procedure will propagate out to the variables in those positions when it is called.

The operative word here being "variables." It cannot change a constant.

Try this:

declare
   @CandID int,
   @Status int;
EXEC CandidatesloginMaster_CheckSecurityCredential 
   @CandID OUT, 'User', 'pwd', 'User123', 'User123', @Status OUT;

MSSQL, for what reason I simply cannot fathom, requires you to use the "out" designation not just when you define the procedure, but also when you call the procedure.