Using "OUT" Parameter With Stored Procedure In ASP.NET


This article introduces you to creating stored procedures for SQL Server 2008 and executing the stored procedure from C# code. Some stored procedures return values through parameters. When a parameter in a SQL statement or stored procedure is declared as out, the value of the parameter is returned back to the caller. First, we create a database table; after that create a stored procedure with an out parameter for insertiing records in the table.

Creating Table in SQL Server Database

Now create a table named UserDetail with the columns UserName, Email and Country. The table looks as below.


Creating a Stored Procedure with Out parameter

Now create a stored procedure with an out parameter to insert data into the table. We create an error out parameter.

  1. USE[Rohatash]  
  2. GO  
  3. /****** Object:  StoredProcedure [dbo].[spuserdetail]    Script Date: 01/25/2012 01:37:54 ******/  
  5. GO  
  7. GO  
  8. ALTER PROCEDURE[dbo].[spuserdetail]  
  9. @UserName varchar(50),  
  10.     @Password varchar(50),  
  11.     @Email varchar(50),  
  12.     @Country varchar(50),  
  13.     @ERROR VARCHAR(100) OUT  
  14. AS  
  15. BEGIN  
  17. IF NOT EXISTS(SELECT * FROM UserDetail WHERE UserName = @UserName) //  To Check UserName is exits or not  
  18. BEGIN  
  19. INSERT INTO UserDetail(UserName, [Password], Email, Country)  
  20. VALUES(@UserName, @Password, @Email, @Country)  
  21. SET @ERROR = @UserName + ' Registered Successfully'  
  22. END  
  23. ELSE  
  24. BEGIN  
  25. SET @ERROR = @UserName + ' Already Exists'  
  26. END  
  27. END  


In the above stored procedure, error is the out parameter and other are the input parameter. In this stored procedure we check UserName; if the UserName exists in the table then it will return the message as an Output Parameter.

  1. SET @ERROR=@UserName + ' Already Exists'

If the UserName does not exist in the table then it will return the message as an Output Parameter.

  1. SET @ERROR=@UserName+' Registered Successfully'

Executing the stored procedure from C# code

In order to demonstrate the process of executing a stored procedure from a C#, create a new web application project in Visual Studio 2010. Add using statements above the namespace declaration to enable the use of non-fully qualified references to other namespace types.

Now add the following namespace.

  1. using System.Data.SqlClient;
  2. using System.Data;

Now write the connection string to connect to the database.

  1. string strConnection = "Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;";

Now I need to display that output parameter message during user registration in ASP.NET. How to get that output parameter returned by a SQL query. For a sample design your aspx page might be like this:

aspx page

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication117.WebForm1" %>  
  2.     <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">  
  3.     <html xmlns="">  
  5.     <head runat="server">  
  6.         <title></title>  
  7.     </head>  
  9.     <body>  
  10.         <form id="form1" runat="server">  
  11.             <div> UserName:  
  12.                 <asp:TextBox ID="UserNameTextBox" runat="server"></asp:TextBox> <br /> <br /> Password:  
  13.                 <asp:TextBox ID="PasswordTextBox" runat="server" TextMode="Password"></asp:TextBox> <br /> <br /> Confirm Password:  
  14.                 <asp:TextBox ID="ConfirmPasswordTextBox" runat="server" TextMode="Password"></asp:TextBox> <br /> <br /> Email:  
  15.                 <asp:TextBox ID="EmailTextBox" runat="server"></asp:TextBox> <br /> <br /> Country:  
  16.                 <asp:TextBox ID="CountryTextBox" runat="server"></asp:TextBox> <br /> <br />  
  17.                 <asp:Button ID="SaveButton" runat="server" Text="Save" onclick="SaveButton_Click" /> <span style="color:Red; font-weight :bold"> <asp:Label ID="lblErrorMsg" runat="server"></asp:Label></span> </div>  
  18.         </form>  
  19.     </body>  
  21.     </html>  


To get output parameters in ASP.NET we need to write statements like this.

  1. cmd.Parameters.Add("@ERROR", SqlDbType.Char, 500);  
  2.         cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;  
  3.         message = (string)cmd.Parameters["@ERROR"].Value;  

In Codebehind write the following code in the SaveButton_Click like this.


  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data.SqlClient;  
  8. using System.Data;  
  9. namespace WebApplication117 {  
  10.     public partial class WebForm1: System.Web.UI.Page {  
  11.         private string message = string.Empty;  
  12.         protected void Page_Load(object sender, EventArgs e) {}  
  13.         protected void SaveButton_Click(object sender, EventArgs e) {  
  14.             if (PasswordTextBox.Text == ConfirmPasswordTextBox.Text) {  
  15.                 string UserName = UserNameTextBox.Text;  
  16.                 string Password = PasswordTextBox.Text;  
  17.                 string ConfirmPassword = ConfirmPasswordTextBox.Text;  
  18.                 string Email = EmailTextBox.Text;  
  19.                 string Country = CountryTextBox.Text;  
  20.                 SqlConnection con = new SqlConnection("Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;");  
  21.                 con.Open();  
  22.                 SqlCommand cmd = new SqlCommand("spuserdetail", con);  
  23.                 cmd.CommandType = CommandType.StoredProcedure;  
  24.                 cmd.Parameters.AddWithValue("@UserName", UserName);  
  25.                 cmd.Parameters.AddWithValue("@Password", Password);  
  26.                 cmd.Parameters.AddWithValue("@Email", Email);  
  27.                 cmd.Parameters.AddWithValue("@Country", Country);  
  28.                 cmd.Parameters.Add("@ERROR", SqlDbType.Char, 500);  
  29.                 cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;  
  30.                 cmd.ExecuteNonQuery();  
  31.                 message = (string) cmd.Parameters["@ERROR"].Value;  
  32.                 con.Close();  
  33.             } else {  
  34.                 Page.RegisterStartupScript("UserMsg""<Script language='javascript'>alert('" + "Password mismatch" + "');</script>");  
  35.             }  
  36.             lblErrorMsg.Text = message;  
  37.         }  
  38.     }  
  39. }  

Now run the application and test it.


Now insert the data and click on the Save Button.

SET @ERROR=@UserName+' Registered Successfully'


The above message shows UserName with a message which is defined in the stored procedure as an output parameter. If we enter the same UserName again than it will display the following message as an output parameter.

SET @ERROR=@UserName + ' Already Exists'


Some Helpful Resources