Procedure or function 'SP' expects parameter '@param', which was not supplied

0
votes

I am facing this error from my C# code. Executing the stored procedure in SQL Server is no problem.

Procedure or function 'SP' expects parameter '@outputparam', which was not supplied.

public string function1(int param1, string param2)
{
    SqlDataAdapter sqladapter = new SqlDataAdapter();
    SqlConnection conn = new SqlConnection(strcon);
    SqlCommand cmd;

    cmd = new SqlCommand("SP", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@param1", param1);
    cmd.Parameters.AddWithValue("@param2", param2);

    SqlParameter returnParameter = cmd.Parameters.Add("@outputparam", SqlDbType.NVarChar);
    returnParameter.Direction = ParameterDirection.ReturnValue;

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

    return cmd.Parameters["@outputparam"].Value.ToString();
}

CREATE PROCEDURE [dbo].[SP] 
    (@param1 int,
     @param2 nvarchar(50),
     @outputparam nvarchar(20) OUTPUT)
1
c#sql-servertsqlstored-procedures
Thats not a returnvalue, thats an output parameter. Change the parameter type. - Dale K
And I highly recommend not using addwithvalue - Dale K
Tip: You can use an explicit initializer with Add to handle additional options, e.g. sqlCommand.Parameters.Add( new SqlParameter() { ParameterName = "@Filename", SqlDbType = SqlDbType.VarChar, Size = 256, Direction = System.Data.ParameterDirection.InputOutput, Value = "Foo.bar" } );. - HABO

1 Answers

1
votes

Try these code

SqlParameter returnParameter = cmd.Parameters.Add("@outputparam", DbType.String);
returnParameter.Direction = ParameterDirection.ReturnValue;

change to

cmd.Parameters.Add("@outputparam", DbType.String, ParameterDirection.Output);

and

return cmd.Get<string>("@outputparam");