Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to use multiple output parameters in SQL Server Stored Procedure in ASP.NET

Author: Alanna Kremer
Download Source Code : 1380_MultipleOutputParametersInSP.zip

Multiple output parameters can be used in a SQL Server database stored procedure to get multiple values in ASP.NET.

 

This article is the continuation of previous articles on stored procedure and its usage. In my previous articles on DevASP.NET, I have explained how we can get latest inserted record identity value, how to write multiple queries in stored procedure to insert and update records and how to write multiple select statements in a stored procedure. This article will explain how to use multiple output parameters in a stored procedure and how we can retrieve these values in ADO.NET code.

As we can use multiple input parameters in a stored procedure to insert record or to give parameters for WHERE clause, we can also use multiple output parameters in stored procedure using OUTPUT keyword. We have to define data type for each output parameter and have to use OUTPUT keyword for each output parameter. At the end of store procedure, you can return each output parameter. In ADP.NET code you can retrieve value from each output parameter by adding parameters for stored procedure and setting their direction as Output.

 

  1. Open Visual Studio 2010
  2. File > New > Web Site
  3. Visual C# or Visual Basic > ASP.NET Empty Web Site > Click Ok
  4. Website > Add New Item > Web Form > Click Add
  5. Add code below in your Web Form

    <table>
        <tr>
            <td>
                EmpName:
            </td>
            <td>
                <asp:TextBox ID="txtEmpName" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                Age:
            </td>
            <td>
                <asp:TextBox ID="txtAge" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                Last Name:
            </td>
            <td>
                <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                First Name:
            </td>
            <td>
                <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                City:
            </td>
            <td>
                <asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                State:
            </td>
            <td>
                <asp:TextBox ID="txtState" runat="server"></asp:TextBox>
            </td>
        </tr>
    </table>
    <asp:Button ID="btnInsert" runat="server"
        Text="Insert and Retrive Output values"
        onclick="btnInsert_Click" />
    <br />
    <asp:Label ID="lblID" runat="server" ></asp:Label>
    <br />
    <asp:Label ID="lblCount" runat="server" ></asp:Label>
    <br />
    <asp:Label ID="lblCount2" runat="server" ></asp:Label>
    <br />
    <asp:Label ID="lblFName" runat="server" ></asp:Label>
    <br />
    <asp:Label ID="lblMessage" runat="server" ></asp:Label>

     
  6. Write a stored procedure for multiple output parameters


    CREATE PROCEDURE dbo.MultipleOutput
    (
           @ID int OUTPUT,
           @Count int OUTPUT,
           @Count2 int OUTPUT,
           @FName nvarchar(50) OUTPUT,
           @LastName nvarchar(50),
           @FirstName nvarchar(50),
           @City nvarchar(30),
           @State nvarchar(10)
           )
    AS
           INSERT Person
                  (
                  LastName,
                  FirstName,
                  City,
                  State
                  )
           VALUES
                  (
                  @LastName,
                  @FirstName,
                  @City,
                  @State
                  )
           SET @ID = SCOPE_IDENTITY()
           SELECT @Count = COUNT(*) FROM Person
           SELECT @Count2 = COUNT(*) FROM Person WHERE State = @State
           SELECT @FName = FirstName FROM Person WHERE ID = 7
           RETURN @ID
           RETURN @Count
           RETURN @Count2
           RETURN @FName
     
    There are four output parameters in this stored procedure. I have defined four output parameters for different values.
     
  7. Open code behind file and include following namespace.

    C#
     
    using System.Data;
    using System.Data.SqlClient;
     
    VB.NET
     
    Imports System.Data
    Imports System.Data.SqlClient
     
  8. Write code below in button click event

    C#
     
    protected void btnInsert_Click(object sender, EventArgs e)
    {
        string lastname = txtLastName.Text;
        string firstname = txtFirstName.Text;
        string city = txtCity.Text;
        string state = txtState.Text;
     
        string constring = "Data Source=Local;Initial Catalog=Sample;Integrated Security=True";
        SqlConnection conn = new SqlConnection(constring);
        SqlCommand cmd = new SqlCommand("MultipleOutput", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output;
        cmd.Parameters.Add("@Count", SqlDbType.Int).Direction = ParameterDirection.Output;
        cmd.Parameters.Add("@Count2", SqlDbType.Int).Direction = ParameterDirection.Output;
        cmd.Parameters.Add("@FName", SqlDbType.NVarChar, 50).Direction = ParameterDirection.Output;
        cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = lastname;
        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = firstname;
        cmd.Parameters.Add("@City", SqlDbType.NVarChar, 30).Value = city;
        cmd.Parameters.Add("@State", SqlDbType.NVarChar, 10).Value = state;
     
        int id = 0;
        int count = 0;
        int count2 = 0;
        string fName = "";
     
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            id = Convert.ToInt32(cmd.Parameters["@ID"].Value);
            count = Convert.ToInt32(cmd.Parameters["@Count"].Value);
            count2 = Convert.ToInt32(cmd.Parameters["@Count2"].Value);
            fName = (cmd.Parameters["@FName"].Value).ToString();
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
        }
        finally
        {
            conn.Close();
        }
     
        lblID.Text = "Latest Inserted Record ID: " + id.ToString();
        lblCount.Text = "Total number of records: " + count.ToString();
        lblCount2.Text = "Total number of records for " + state + ": " + count2.ToString();
        lblFName.Text = "FirstName for Record ID 7: " + fName;
    }
     
    VB.NET
     
    Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnInsert.Click
        Dim lastname As String = txtLastName.Text
        Dim firstname As String = txtFirstName.Text
        Dim city As String = txtCity.Text
        Dim state As String = txtState.Text
     
        Dim constring As String = "Data Source=Local;Initial Catalog=Sample;Integrated Security=True"
        Dim conn As New SqlConnection(constring)
        Dim cmd As New SqlCommand("MultipleOutput", conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output
        cmd.Parameters.Add("@Count", SqlDbType.Int).Direction = ParameterDirection.Output
        cmd.Parameters.Add("@Count2", SqlDbType.Int).Direction = ParameterDirection.Output
        cmd.Parameters.Add("@FName", SqlDbType.NVarChar, 50).Direction = ParameterDirection.Output
        cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = lastname
        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = firstname
        cmd.Parameters.Add("@City", SqlDbType.NVarChar, 30).Value = city
        cmd.Parameters.Add("@State", SqlDbType.NVarChar, 10).Value = state
       
        Dim id As Integer = 0
        Dim count As Integer = 0
        Dim count2 As Integer = 0
        Dim fName As String = ""
     
        Try
            conn.Open()
            cmd.ExecuteNonQuery()
            id = Convert.ToInt32(cmd.Parameters("@ID").Value)
            count = Convert.ToInt32(cmd.Parameters("@Count").Value)
            count2 = Convert.ToInt32(cmd.Parameters("@Count2").Value)
            fName = (cmd.Parameters("@FName").Value).ToString()
        Catch ex As Exception
            lblMessage.Text = ex.Message
        Finally
            conn.Close()
        End Try
     
        lblID.Text = "Latest Inserted Record ID: " + id.ToString()
        lblCount.Text = "Total number of records: " + count.ToString()
        lblCount2.Text = "Total number of records for " + state + ": " + count2.ToString()
        lblFName.Text = "FirstName for Record ID 7: " + fName
    End Sub
     
    Get values from text boxes to insert into table. Connect to your server and set connection using SqlConnection class. . Pass stored procedure name and connection object in SqlCommand constructor and set command type as stored procedure. Add all parameters for stored procedure and set direction as Output for output parameters in stored procedure. Open connection, call the ExecuteNonQuery() method of SqlCommand object and retrieve values. 
     
  9. Press F5, insert values and click on button to execute stored procedure with multiple output parameters.

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to create DataSet Relations in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net