Return a boolean value from a stored procedure using a bit

Question

This is my code for a stored procedure that checks for email.

ALTER PROCEDURE [dbo].[usp_CheckEmailMobile](@Name VARCHAR(50), @Email NVARCHAR(50), @Password NVARCHAR(50), @CountryCode INT, @Mobile VARCHAR(50), @Result BIT OUTPUT) AS BEGIN IF EXISTS (SELECT COUNT (*) FROM AUser WHERE [Email] = @Email AND [Mobile] = @Mobile) SELECT 'FALSE'; --Email &/or Mobile does not exist in database ELSE --Insert the record & register the user INSERT INTO [AUser] ([Name], [Email], [Password], [CountryCode], [Mobile]) VALUES (@Name, @Email, @Password, @CountryCode, @Mobile) END 

How to assign the result of this SP @Result (its output parameter)?

here is the cs CODE: Where will I be wron in this?

  protected void btnRegister_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]); con.Open(); SqlCommand Cmd = new SqlCommand("usp_CheckEmailMobile", con); Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "Registration"; Cmd.Parameters.AddWithValue("@Name", txtName.Text); Cmd.Parameters.AddWithValue("@Email", txtEmailAddress.Text); Cmd.Parameters.AddWithValue("@Password", txtPassword.Text); Cmd.Parameters.AddWithValue("@CountryCode", ddlCountryCode.Text); Cmd.Parameters.AddWithValue("@Mobile", txtMobileNumber.Text); //Cmd.Parameters.Add("@Result", DbType.Boolean); SqlParameter sqlParam = new SqlParameter("@Result", DbType.Boolean); //sqlParam.ParameterName = "@Result"; //sqlParam.DbType = DbType.Boolean; sqlParam.Direction = ParameterDirection.Output; Cmd.Parameters.Add(sqlParam); Cmd.ExecuteNonQuery(); con.Close(); Response.Write(Cmd.Parameters["@Result"].Value); } 

went through this, help dint ... How to start a stored procedure with an OUTPUT parameter from C #?

Answer 1

You just set it as a regular variable like this

  ALTER PROCEDURE [dbo].[usp_CheckEmailMobile](@Name VARCHAR(50), @Email NVARCHAR(50), @Password NVARCHAR(50), @CountryCode INT, @Mobile VARCHAR(50), @Result BIT OUTPUT) AS BEGIN IF EXISTS (SELECT COUNT (*) FROM AUser WHERE [Email] = @Email AND [Mobile] = @Mobile) BEGIN SELECT 'FALSE'; --Email &/or Mobile does not exist in database @Result = 0 END ELSE BEGIN --Insert the record & register the user INSERT INTO [AUser] ([Name], [Email], [Password], [CountryCode], [Mobile]) VALUES (@Name, @Email, @Password, @CountryCode, @Mobile) @Result = 1 END END 

For server side code

 SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]); con.Open(); SqlCommand Cmd = new SqlCommand("usp_CheckEmailMobile", con); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.AddWithValue("@Name", txtName.Text); Cmd.Parameters.AddWithValue("@Email", txtEmailAddress.Text); Cmd.Parameters.AddWithValue("@Password", txtPassword.Text); Cmd.Parameters.AddWithValue("@CountryCode", ddlCountryCode.Text); Cmd.Parameters.AddWithValue("@Mobile", txtMobileNumber.Text); SqlParameter sqlParam = new SqlParameter("@Result", DbType.Boolean); sqlParam.Direction = ParameterDirection.Output; Cmd.Parameters.Add(sqlParam); Cmd.ExecuteNonQuery(); con.Close(); Response.Write(Cmd.Parameters["@Result"].Value); 

Answer 2

Do it:

 ALTER PROCEDURE [dbo].[usp_CheckEmailMobile](@Name VARCHAR(50), @Email NVARCHAR(50), @Password NVARCHAR(50), @CountryCode INT, @Mobile VARCHAR(50), @Result BIT OUTPUT) AS BEGIN Declare @result bit IF EXISTS (SELECT COUNT (*) FROM AUser WHERE [Email] = @Email AND [Mobile] = @Mobile) Begin Set @result=0; --Email &/or Mobile does not exist in database End ELSE Begin --Insert the record & register the user INSERT INTO [AUser] ([Name], [Email], [Password], [CountryCode], [Mobile]) VALUES (@Name, @Email, @Password, @CountryCode, @Mobile) Set @result=1 --True End Select @result as Result END 

cs code:

 bool? IsSuccess= YourDBObject.usp_CheckEmailMobile(all params).FirstOrDefault().Result;