C# - SQL procedure that takes an argument and gives a value back

Question

I'm writing an app for a class, and I've got working stored procedures for insert, delete, alter, and view.

But this one takes an argument and then gives another value back. How do I implement this in my code?

This is the SQL:

USE [ADK_DB]
GO
/****** Object:  StoredProcedure [IO].[usp_ukupno_donacija_prema_tipu_krg]    Script Date: 3/31/2020 6:52:14 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [IO].[usp_ukupno_donacija_prema_tipu_krg]
@krg_id smallint
as
begin
declare @sum int
select @sum = [IO].[fn_broj_donacija_tip_krg](@krg_id)
print @sum
end;

And this is what I got for code:

    public void DohvatiDonacijePoKG(string krgId)
    {
        SqlCommand command = new SqlCommand("[IO].[usp_ukupno_donacija_prema_tipu_krg]", cnn);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@krg_id", SqlDbType.VarChar)).Value = krgId;

        // Return the output from the database

        try
        {
            command.ExecuteNonQuery();
            MessageBox.Show("Success.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        catch (Exception ex)
        {
            MessageBox.Show("Execption: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

user_5070879 · Answer

One way is to use an output parameter:

ALTER procedure [IO].[usp_ukupno_donacija_prema_tipu_krg]
@krg_id smallint, @sum INT OUT
as
begin
   set @sum = [IO].[fn_broj_donacija_tip_krg](@krg_id);
end;

A stored procedure could return values in a few ways:

SELECT resultset
output parameter
RETURN ...; (only single INT value)
PRINT message (you need to intercept it - SqlConnection.InfoMessage)

user_10532500 · Answer

In C# you can pass the output parameter like the below one. You can refer to How To Use Output Parameter In Stored Procedure In C#.

cmd.Parameters.Add("@Guid", SqlDbType.VarChar, 100);
cmd.Parameters["@Guid"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@ClientName", SqlDbType.VarChar, 100);
cmd.Parameters["@ClientName"].Direction = ParameterDirection.Output;

C# - SQL procedure that takes an argument and gives a value back

2 Answers