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

Introduction

This article introduces you how to create stored procedures in SQL Server 2008 and how to execute stored procedures in C# from an ASP.NET Web application. 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. In this article and code sample, we will see how to execute a stored procedure and return a value via the OUT parameter.

First, we create a database table; after that create a stored procedure with an out parameter for inserting records in the table.

Creating a Table in SQL Server Database

Create a table named UserDetail with 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.

USE[Rohatash]
GO
/****** Object: StoredProcedure [dbo].[spuserdetail] Script Date: 01/25/2012 01:37:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE[dbo].[spuserdetail]
@UserName varchar(50),
@Password varchar(50),
@Email varchar(50),
@Country varchar(50),
@ERROR VARCHAR(100) OUT
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM UserDetail WHERE UserName = @UserName) // To Check UserName is exits or not
BEGIN
INSERT INTO UserDetail(UserName, [Password], Email, Country)
VALUES(@UserName, @Password, @Email, @Country)
SET @ERROR = @UserName + ' Registered Successfully'
END
ELSE
BEGIN
SET @ERROR = @UserName + ' Already Exists'
END
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.

SET @ERROR=@UserName + ' Already Exists'

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

SET @ERROR=@UserName+' Registered Successfully'

Executing the stored procedure from C# code

In order to demonstrate the process of executing a stored procedure in 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 namespaces to your class:

using System.Data.SqlClient;
using System.Data;

Write connection string to connect to the database.

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

Now, I need to display that output parameter message during the user registration in ASP.NET. Let's see how we can get that output parameter returned by a SQL query. For sample purposes, the design of the aspx page looks like this:

aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication117.WebForm1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div> UserName:
<asp:TextBox ID="UserNameTextBox" runat="server"></asp:TextBox> <br /> <br /> Password:
<asp:TextBox ID="PasswordTextBox" runat="server" TextMode="Password"></asp:TextBox> <br /> <br /> Confirm Password:
<asp:TextBox ID="ConfirmPasswordTextBox" runat="server" TextMode="Password"></asp:TextBox> <br /> <br /> Email:
<asp:TextBox ID="EmailTextBox" runat="server"></asp:TextBox> <br /> <br /> Country:
<asp:TextBox ID="CountryTextBox" runat="server"></asp:TextBox> <br /> <br />
<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>
</form>
</body>
</html>

To get output parameters in ASP.NET, we need to write the statement like this.

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

In code behind, write the following code on the SaveButton_Click.

Code behind

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
namespace WebApplication117 {
public partial class WebForm1: System.Web.UI.Page {
private string message = string.Empty;
protected void Page_Load(object sender, EventArgs e) {}
protected void SaveButton_Click(object sender, EventArgs e) {
if (PasswordTextBox.Text == ConfirmPasswordTextBox.Text) {
string UserName = UserNameTextBox.Text;
string Password = PasswordTextBox.Text;
string ConfirmPassword = ConfirmPasswordTextBox.Text;
string Email = EmailTextBox.Text;
string Country = CountryTextBox.Text;
SqlConnection con = new SqlConnection("Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;");
con.Open();
SqlCommand cmd = new SqlCommand("spuserdetail", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserName", UserName);
cmd.Parameters.AddWithValue("@Password", Password);
cmd.Parameters.AddWithValue("@Email", Email);
cmd.Parameters.AddWithValue("@Country", Country);
cmd.Parameters.Add("@ERROR", SqlDbType.Char, 500);
cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
message = (string) cmd.Parameters["@ERROR"].Value;
con.Close();
} else {
Page.RegisterStartupScript("UserMsg", "<Script language='javascript'>alert('" + "Password mismatch" + "');</script>");
}
lblErrorMsg.Text = message;
}
}
}

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 a 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

8 Comments

Join the conversation! Your thoughts help the community grow.

Pranav Amrutkar7y ago
Very nice article Rohatash...Good one...
Rajveer singh11y ago
Nice Article..........
xmen roma11y ago
Thank you very much.
Nihar Ranjan Behera11y ago
Already user exist doesn't work
Upendra Pratap Shahi11y ago
nice....
FAROOKH MANSURI11y ago
nice article
nupur kadam13y ago
helped me alot
nupur kadam13y ago
Thank you very much