Using "OUT" Parameter With Stored Procedure in MVC

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();
            }

        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;