Keep getting an error: Procedure or function 'usp_StoredProcName' expects parameter '@inputVal', which was not supplied

Question

I have a simple stored procedure which expects 1 input parameter and has 2 output parameters:

CREATE PROCEDURE [dbo].[usp_StoredProcName]
    @inputVal nvarchar(255),
    @isError bit OUTPUT,
    @errorInfo nvarchar(255) OUTPUT
AS BEGIN
DECLARE @totalRow int = 0;
DECLARE @inputValID uniqueidentifier;

SET @isError = 1;
SET @errorInfo = '';

SELECT @inputValID = [inputValID]
FROM testTable
WHERE inputVal = @inputVal;

IF @inputValID IS NULL
BEGIN
    SET @isError = 0;
    SET @errorInfo = 'inputVal not found';
    RETURN
END
END

To execute this parametrized stored procedure, I have used couple of C# methods and they all return this error:

Procedure or function 'sp_StoredProcName' expects parameter '@inputVal', which was not supplied.

Method 1 (to call parametrized stored procedure):

using (SqlConnection con = new SqlConnection(myFullConncectionStringToDB))
{
   using (SqlCommand cmd = new SqlCommand("sp_StoredProcName", con))
   {
      cmd.CommandType = CommandType.StoredProcedure;

      cmd.Parameters.AddWithValue("@inputVal", "MyParamVal_12345");

      cmd.Parameters["@isError"].Direction = ParameterDirection.Output;
      cmd.Parameters["@errorInfo"].Direction = ParameterDirection.Output;

      con.Open();
      cmd.ExecuteNonQuery();

      var isError = cmd.Parameters["@isError"].Value;
      var errInfo = cmd.Parameters["@errorInfo"].Value;
      con.Close();
   }
}

Method 2 (to call parametrized stored procedure):

SqlConnection con = new SqlConnection(myFullConncectionStringToDB);
SqlCommand cmd = new SqlCommand("sp_StoredProcName", con);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter in_parm = new SqlParameter("@inputVal", SqlDbType.NVarChar);
in_parm.Size = 255;
in_parm.Value = "MyParamVal_12345";
in_parm.Direction = ParameterDirection.Input;
cmd.Parameters.Add(in_parm);

SqlParameter out_parm = new SqlParameter("@errorInfo", SqlDbType.NVarChar);
out_parm.Size = 255;
out_parm.Direction = ParameterDirection.Output; 
cmd.Parameters.Add(out_parm);

SqlParameter out_parm1 = new SqlParameter("@isError", SqlDbType.Bit);
out_parm1.Direction = ParameterDirection.Output; 
cmd.Parameters.Add(out_parm1);

con.Open();
cmd.ExecuteNonQuery();
con.Close();

Both of the above c# methods return the same error:

Procedure or function 'sp_StoredProcName' expects parameter '@inputVal', which was not supplied.

Please tell me what am I doing wrong here in my C# code to execute the stored procedure.

I am clearly passing the parameter value in both of my methods but can't figure out why it keeps displaying this error.

Thank you so much!

You have inputVal declared as both a parameter and a proc variable ? — David Brabant
Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
thank you. Yes that's a good pick regarding the sp_ prefix but the actual stored proc doesn't begin with the sp_ prefix. I've written here just for illustration purposes. — theITvideos

David David · Accepted Answer · 2013-09-04T14:13:52

You are setting @InputVal as an input parameter in the line right after Create Procedure

CREATE PROCEDURE [dbo].[sp_StoredProcName]

@inputVal nvarchar(255),

and then creating it again further down as a UniqueIdentifier.

DECLARE @inputVal uniqueidentifier;

That's not valid syntax. Change one or the other. If you don't want to take an input parameter (which would eliminate the error you're asking about), delete the first.

Keep getting an error: Procedure or function 'usp_StoredProcName' expects parameter '@inputVal', which was not supplied

1 Answers