Wrong Value Returned

Hello Ramco,

The better way you can write this way,

ALTER PROCEDURE [dbo].[sp_Department]
    @Action VARCHAR(1),
    @DocEntryNo INT = NULL,
    @Description VARCHAR(50) = NULL,
    @ShortName VARCHAR(15) = NULL,
    @Status BIT = NULL,
    @Success BIT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- Initialize @Success
    SET @Success = 0;

    --INSERT
    IF @Action = 'I'
    BEGIN
        INSERT INTO dbo.Department(Description, ShortName, Status, CreatedBy, CreatedOn, UpdatedBy, UpdatedOn)
        VALUES (@Description, @ShortName, 1, 1, GETDATE(), 1, GETDATE());
        
        -- Check if the INSERT affected any rows
        IF @@ROWCOUNT > 0
            SET @Success = 1;
    END

    --DELETE
    IF @Action = 'D'
    BEGIN
        UPDATE dbo.Department
        SET Status = 0, UpdatedOn = GETDATE(), UpdatedBy = 1 
        WHERE DocEntry = @DocEntryNo;
        
        -- Check if the UPDATE affected any rows
        IF @@ROWCOUNT > 0
            SET @Success = 1;
    END
END

The issue you’re encountering is due to the placement of the IF @@ROWCOUNT = 0 check. This check is executed after both the INSERT and UPDATE statements, and it will set @Success to 0 if the last statement did not affect any rows.

To fix this, you should move the IF @@ROWCOUNT = 0 check inside each conditional block (IF @Action = 'I' and IF @Action = 'D'). This way, it will only check the @@ROWCOUNT immediately after the relevant INSERT or UPDATE statement. Here’s how you can modify your stored procedure:

ALTER PROCEDURE [dbo].[sp_Department]
    @Action VARCHAR(1),
    @DocEntryNo INT = NULL,
    @Description VARCHAR(50) = NULL,
    @ShortName VARCHAR(15) = NULL,
    @Status BIT = NULL,
    @Success BIT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- INSERT
    IF @Action = 'I'
    BEGIN
        INSERT INTO dbo.Department (Description, ShortName, status, createdby, createdon, updatedby, updatedon)
        VALUES (@Description, @ShortName, 1, 1, GETDATE(), 1, GETDATE());

        IF @@ROWCOUNT = 0
            SET @Success = 0;
        ELSE
            SET @Success = 1;
    END

    -- DELETE
    IF @Action = 'D'
    BEGIN
        UPDATE dbo.Department
        SET status = 0, updatedon = GETDATE(), updatedby = 1
        WHERE docentry = @DocEntryNo;

        IF @@ROWCOUNT = 0
            SET @Success = 0;
        ELSE
            SET @Success = 1;
    END
END

 

This way, the @Success variable is set based on whether the INSERT or UPDATE statement affected any rows. 

Thanks