Stored procedure with out parameter

Question

I have a stored procedure:

ALTER PROCEDURE [dbo].[sp_CheckEmailAvailability] -- Add the parameters for the stored procedure here ( @Email VARCHAR(50)=null, @Count int OUTPUT ) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT @Count=COUNT(*) from dbo.tx_UserPersonalDetails where s_Email=@Email END 

I have the following code on my aspx.cs page: -

 SqlCommand cmd = new SqlCommand("[dbo].[sp_CheckEmailAvailability]", objcon); int result = 0; try { cmd.CommandType = CommandType.StoredProcedure; SqlParameter parm = new SqlParameter("@Email", SqlDbType.VarChar); parm.Value = txtUserName.Text.ToString(); parm.Direction = ParameterDirection.Input; cmd.Parameters.Add(parm); SqlParameter parm1 = new SqlParameter("@Count", SqlDbType.Int); // parm1.Value = txtUserName.Text.ToString(); parm.Direction = ParameterDirection.Output; cmd.Parameters.Add(parm1); cmd.Connection.Open(); result=cmd.ExecuteNonQuery(); if (result>0) { lblAvailText.Text = "Email id is in use"; } else { lblAvailText.Text = "Email id is Available"; } } catch (SqlException sql) { } finally { cmd.Connection.Close(); } 

When I run the code, I get the error message: -

The formal parameter "@Email" was not declared as an OUTPUT parameter, but the actual parameter was passed in the requested output.

Please help me with this.

Answer 1

Change

 parm.Direction = ParameterDirection.Output; 

to

 parm1.Direction = ParameterDirection.Output; 

You installed the wrong SqlParam.

parm is used for the @Email parameter, which is initially correctly specified as "Enter", but then when you create parm1, you do not specify its direction, you set the direction of movement.

This is why you should use good naming conventions.

Answer 2

Alternative way - try using RETURN -

 ALTER PROCEDURE [dbo].[sp_CheckEmailAvailability] ( @Email VARCHAR(50) ) AS BEGIN RETURN ( SELECT COUNT(1) FROM dbo.tx_UserPersonalDetails WHERE s_Email = @Email ) END