Output parameter in SQL not returning any value

Question

I am trying to return a value from a stored procedure in .net, but it does not return any value.

Here is my stored procedure...

ALTER procedure [dbo].[usp_CreateUser]
    @UserName varchar(50),
    @Result bit output
as
begin
    declare @temp varchar(50)

    select @temp = UserName  
    from tbl_UserLoginDetails
    where UserName = @UserName

    if ((@temp is null) or (@temp=''))   
       set @Result = 1
    else
       set @Result = 0

    return @Result
end

And here is my .net code..

SqlConnection vConn = new SqlConnection(ConnectionString);

SqlCommand vComm = new SqlCommand("usp_CreateUser",vConn);
vComm.CommandType = CommandType.StoredProcedure;

vComm.Parameters.AddWithValue("UserName","Tanuj");

SqlParameter retval = vComm.Parameters.Add("@Result", SqlDbType.Bit);
retval.Direction = ParameterDirection.ReturnValue;

vConn.Open();
vComm.ExecuteNonQuery();
vConn.Close();

Console.WriteLine(vComm.Parameters["@Result"].Value);

I am getting the following error...

Procedure or function 'usp_CreateUser' expects parameter '@Result', which was not supplied.

I am expecting it to return either 0 or 1.

Thanks in advance

Solomon Rutzky · Accepted Answer · 2015-01-07T05:50:56.667

You are confusing OUTPUT parameters with Return Values.

A Return Value is an INT that is passed back via the RETURN command. This should be used for an "exit code" about the execution of the proc and not actual data (though people often use it to pass back data as you are trying to do).

An OUTPUT parameter can be any data type and is populated via a SET or SELECT. As with input parameters, not specifying a default value means that it is required.

The error you are getting is because you have declared @Result as an OUTPUT parameter in the stored proc while declaring it instead as the ReturnValue in the .NET code (and hence you are not specifying any OUTPUT parameter in the .NET code).

You should:

Change return @Result to be just RETURN;
Change ParameterDirection to Output
Access the value via (bool)retval.Value

Also, your stored procedure code is doing unnecessary work. It just needs to be:

IF (EXISTS(
           SELECT 1
           FROM   tbl_UserLoginDetails
           WHERE  UserName = @UserName
   ))
BEGIN
  SET @Result = 0;
END;
ELSE
BEGIN
  SET @Result = 1;
END;

RETURN;

Also, just FYI, you really shouldn't use Parameters.AddWithValue() as it can cause problems if .NET doesn't guess the datatype correctly from the current value. Instead, it is best to use Parameters.Add() so that you can specify a SqlDbType, just like you are doing for "retval".

Output parameter in SQL not returning any value

1 Answers1