Create procedure with Output Parameter and display in Label using C# in ASP.Net

Hi PRA,

Refer the below sample.

HTML

<asp:TextBox runat="server" ID="Name" />
<br />
<asp:Button ID="AddGost" Text="AddGost" runat="server" OnClick="AddGost_Click" />
<br />
<asp:Label ID="IdText" runat="server" />

C#

protected void AddGost_Click(object sender, EventArgs e)
{
    IdText.Text = addgost(Name.Text);
}

public string addgost(string name)
{
    string id;
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[1].ConnectionString);
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "GetIdGost";
    cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = name;
    cmd.Parameters.Add("@IdGost", SqlDbType.Int).Direction = ParameterDirection.Output;
    cmd.Connection = con;
    try
    {
        con.Open();
        cmd.ExecuteNonQuery();
        id = cmd.Parameters["@IdGost"].Value.ToString();
    }
    catch (Exception ex) { throw ex; }
    finally
    {
        con.Close();
        con.Dispose();
    }
    return id;
}

SQL

CREATE TABLE TblFirst (Id INT,Name VARCHAR(10))
INSERT INTO TblFirst VALUES(1,'Amonullo')
INSERT INTO TblFirst VALUES(2,'Samandar')
INSERT INTO TblFirst VALUES(5,'Asror')
 
CREATE TABLE TblSecond (Id INT,Name VARCHAR(10))
INSERT INTO TblSecond VALUES(3,'Firuz')
INSERT INTO TblSecond VALUES(4,'Bahrullo')
INSERT INTO TblSecond VALUES(6,'Sadriddin')

CREATE TABLE TblPeople (Id INT,Name VARCHAR(10))

CREATE PROCEDURE GetIdGost
    @Name VARCHAR(25),
    @IdGost INT OUTPUT
AS
BEGIN
	DECLARE @id INT,@d INT,@i INT
    SELECT @id = max(Id) FROM TblFirst
    SELECT @d = max(Id) FROM TblSecond
    IF ISNULL(@id,0) = 0 and ISNULL(@d,0) = 0
    BEGIN
        SET @i = 1
    END
    ELSE IF (isnull(@id,0) > isnull(@d,0))
    BEGIN
        SET @i = @id + 1
    END
    ELSE IF (isnull(@id,0) < isnull(@d,0))
    BEGIN
        SET @i = @d + 1
    END
     
    SET NOCOUNT ON;
    INSERT INTO TblPeople(Id, Name)VALUES(@i, @Name)
    SET @IdGost = @i
    RETURN @IdGost
END