Calling a stored procedure with output parameters - Part 7

Suggested Videos
Part 7 of SQL Server - Identity Column in SQL Server
Part 8 of SQL Server - How to get the last generated identity column value in SQL Server
Part 19 of SQL Server - Stored procedures with output parameters
Part 6 of ADO.NET - Sql injection prevention

In Part 6, we have discussed that sql injection can be prevented by using parameterized queries or stored procedures. We have also seen how to call a stored procedure with input parameters. In this part, we will learn about calling a stored procedure with output parameters.

Our example, will be based on tblEmployees. The script to create this table is shown below. The important point to note here is that, EmployeeId is marked as an identity column. When inserting a row into the table, we need not provide a value for the EmployeeId column. If you want to learn more about identity column, please watch - Part 7 of SQL Server : Identity Column in SQL Server.
Create Table tblEmployees
(
EmployeeId int identity primary key,
Name nvarchar(50),
Gender nvarchar(10),
Salary int
)



Script to insert sample data. Notice, that in the insert statement we are not providing a value for EmployeeId Column.
Insert into tblEmployees values('Mike','Male',5000)
Insert into tblEmployees values('Pam','Female',3500)
Insert into tblEmployees values('John','Male',2350)
Insert into tblEmployees values('Sara','Female',5700)
Insert into tblEmployees values('Steve','Male',4890)
Insert into tblEmployees values('Sana','Female',4500)

1. spAddEmployee stored procedure inserts a row into tblEmployees tables. 
2. @Name, @Gender and @Salary are input parameters.
3. @EmployeeId is an output parameter
4. The stored procedure has got only 2 lines of code with in the body. The first line inserts a row into the tblEmployees table. The second line, gets the auto generated identity value of the  EmployeeId column.
5. This procedure, will later be called by a dot net application.
Create Procedure spAddEmployee  
@Name nvarchar(50),  
@Gender nvarchar(20),  
@Salary int,  
@EmployeeId int Out  
as  
Begin  
 Insert into tblEmployees values(@Name, @Gender, @Salary)  
 Select @EmployeeId = SCOPE_IDENTITY()  
End



At this point, we have done everything that is required for our demo, from a database perspective. Now let's flip to visual studio. Create an asp.net web application. Copy and Paste the following HTML onto a webform.
<table style="border: 1px solid black; font-family:Arial">
    <tr>
        <td>
            Employee Name
        </td>
        <td>
            <asp:TextBox ID="txtEmployeeName" runat="server"></asp:TextBox>
        </td>
    </tr>        
    <tr>
        <td>
            Gender
        </td>
        <td>
            <asp:DropDownList ID="ddlGender" runat="server">
                <asp:ListItem>Male</asp:ListItem>
                <asp:ListItem>Female</asp:ListItem>
            </asp:DropDownList>
        </td>
    </tr> 
    <tr>
        <td>
            Salary
        </td>
        <td>
            <asp:TextBox ID="txtSalary" runat="server"></asp:TextBox>
        </td>
    </tr>       
    <tr>
        <td colspan="2">
            <asp:Button ID="btnSubmit" runat="server" Text="Submit" 
                onclick="btnSubmit_Click" />
        </td>
    </tr>          
    <tr>
        <td colspan="2">
            <asp:Label ID="lblMessage" runat="server"></asp:Label>
        </td>
    </tr>  
</table>

The design of the webform, should be as shown below.


Copy and paste the following code in the code behind page.
protected void btnSubmit_Click(object sender, EventArgs e)
{
    //Read the connection string from Web.Config file
    string ConnectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(ConnectionString))
    {
        //Create the SqlCommand object
        SqlCommand cmd = new SqlCommand("spAddEmployee", con);
        //Specify that the SqlCommand is a stored procedure
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        //Add the input parameters to the command object
        cmd.Parameters.AddWithValue("@Name", txtEmployeeName.Text);
        cmd.Parameters.AddWithValue("@Gender", ddlGender.SelectedValue);
        cmd.Parameters.AddWithValue("@Salary", txtSalary.Text);

        //Add the output parameter to the command object
        SqlParameter outPutParameter = new SqlParameter();
        outPutParameter.ParameterName = "@EmployeeId";
        outPutParameter.SqlDbType = System.Data.SqlDbType.Int;
        outPutParameter.Direction = System.Data.ParameterDirection.Output;
        cmd.Parameters.Add(outPutParameter);

        //Open the connection and execute the query
        con.Open();
        cmd.ExecuteNonQuery();
                
        //Retrieve the value of the output parameter
        string EmployeeId = outPutParameter.Value.ToString();
        lblMessage.Text = "Employee Id = " + EmployeeId;
    }
}

Note: Please make sure to add the following using declarations at the top of the code behind page.
using System.Data.SqlClient;
using System.Configuration;

Now, run the application. Fill in the employee details and click Submit. The Employee row gets added to the database, and the generated EmployeeId is shown on the screen.

12 comments:

  1. I HAVE THE NEXT ERROR
    Procedure or function newDocumentacion has too many arguments specified.
    HOPE YOU CAN HELP ME :)

    ReplyDelete
  2. No mapping exists from object type System.Web.UI.WebControls.TextBox to a known managed provider native type. cmd.ExecteNonQuery();
    i get this error

    ReplyDelete
  3. No mapping exists from object type System.Web.UI.WebControls.TextBox to a known managed provider native type. cmd.ExecteNonQuery();
    i get this error

    ReplyDelete
    Replies
    1. cmd.Parameters.AddWithValue("@Gender", ddlGender.selecteditem.tostring());

      Delete
  4. Followed the video and got exact result ...Thanks Venkat

    ReplyDelete
  5. I just want to thank you for your invaluable services. I love you postings. They are hands-on and easy to follow.

    ReplyDelete
  6. sir,can u please explain the use of scope identity here... and difference between scope identity and @@identity with examples

    ReplyDelete
    Replies
    1. A situation where the scope_identity() and the @@identity functions differ, is if you have a trigger on the table. If you have a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity() function will return the identity created by the query, while the @@identity function will return the identity created by the trigger.

      So, normally you would use the scope_identity() function.

      Delete
  7. Sir, very well understood, thanks for the explanation. But, I have a concern: if the stock procedure returned several values, how to recover all these values?

    ReplyDelete
  8. Control 'txtEmployeeName' of type 'TextBox' must be placed inside a form tag with runat=server.

    ReplyDelete
  9. It was wonderful lesson.
    it works fine but when i add the record it has started adding EmployeeId from 1002,1003 and so on, instead of 8..
    Can you guide what possible mistake i am committing?
    Thanks a lot

    ReplyDelete
  10. This is image

    http://icecream.me/3fb053ce51f51693a0f51a32c1621743

    ReplyDelete

It would be great if you can help share these free resources