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.
===================================
USE [TEST]
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'
====================================
Codebehind
=======================================
string message = string.Empty;
SqlConnection con = new SqlConnection("Data Source=.; uid=sa; pwd=*****;database=Mehedi;");
if (conn.State == 0)
{
conn.Open();
}
{
conn.Open();
}
SqlCommand cmd = new SqlCommand("USP_GETUSER", 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;
