Stored procedure should return value

It looks like you want to return a value from the stored procedure to indicate whether the record was successfully added/updated, and then display a success message.

To achieve this, you can modify your stored procedure to return an output parameter, and then retrieve that value in your C# code.

Here's an updated version of your code:

 

Create PROCEDURE [dbo].[sp_Department]
    @Action VARCHAR(1)
    ,@EntryNo int = Null
    ,@Description VARCHAR(50) = NULL
    ,@ShortName VARCHAR(25) = NULL
    ,@Success BIT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    --INSERT
    IF @Action = 'I'
    BEGIN
        INSERT INTO dbo.Department(Description, createdon, updatedon)
        VALUES (@Description, GETDATE(), GETDATE())
        SET @Success = 1
    END

    --UPDATE
    IF @Action = 'U'
    BEGIN
        UPDATE dbo.Department
        SET Description = @Description, updatedon = GETDATE() WHERE docentry = @EntryNo
        SET @Success = 1
    END

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

    IF @@ROWCOUNT = 0
        SET @Success = 0
END

Csharp COde
 

using (SqlConnection con = new SqlConnection(Common.CommonFunction.cnn_Live))
{
    SqlCommand cmd = new SqlCommand("Sp_Department", con);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@EntryNo", SqlDbType.Int).Value = hdfDeleteID.Value;
    cmd.Parameters.Add("@Action", SqlDbType.NVarChar).Value = "D";
    SqlParameter successParam = cmd.Parameters.Add("@Success", SqlDbType.Bit);
    successParam.Direction = ParameterDirection.Output;

    con.Open();

    cmd.ExecuteNonQuery();

    bool success = (bool)successParam.Value;

    if (success)
    {
        // Display success message
        Console.WriteLine("Record successfully deleted/updated!");
    }
    else
    {
        // Display error message
        Console.WriteLine("Error deleting/updating record!");
    }
}

 

In the updated stored procedure, I added an output parameter @Success of type BIT. This parameter is set to 1 if the operation is successful, and 0 otherwise.

In the C# code, I added a new parameter @Success to the SqlCommand object, and set its direction to Output. After executing the stored procedure, I retrieve the value of the @Success parameter and check if it's true. If it is, I display a success message; otherwise, I display an error message.

Let me know if this helps!