'Procedure or function expects parameter, which was not supplied' error

Question

I have a method in a service that is calling a stored procedure in a SQL Server database and I'm getting an error:

Procedure or function 'GenerateInviteKey' expects parameter '@inviterId', which was not supplied

My stored procedure works when I execute it in SQL Server Mgmt Studio, so I'm pretty sure its my C# method that's causing the error. My code looks like this:

public class Invite
{
        public Int64 InviterId { get; set; }
        public string InviterName { get; set; }
        public string InviteeEmail { get; set; }
}

private string CreateInviteKey(Invite invite)
{
    var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);

    try
    {
        conn.Open();

        var command = new SqlCommand("GenerateInviteKey", conn);
        command.Parameters.Add(new SqlParameter("@inviterId", SqlDbType.BigInt, 0, "inviter_id"));
        command.Parameters.Add(new SqlParameter("@inviteeEmail", SqlDbType.VarChar, 100, "invitee_email"));
        command.Parameters.Add(new SqlParameter("@inviteKey", SqlDbType.VarChar, 30, "invite_key"));

        command.Parameters[0].Value = invite.InviterId;
        command.Parameters[1].Value = invite.InviteeEmail;
        command.Parameters[2].Direction = ParameterDirection.Output;

        command.ExecuteNonQuery();

        var inviteKey = (string)command.Parameters[2].Value;

        return inviteKey;
   }
   catch (Exception ex)
   {
       var error = ex.Message;
       return null;
   }
   finally
   {
       conn.Close();
       conn.Dispose();
   }
}

And the stored procedure looks like this:

ALTER PROCEDURE [dbo].[GenerateInviteKey] 
    @inviterId int,
    @inviteeEmail varchar(100), 
    @inviteKey varchar(30) OUT
AS
BEGIN
    SET NOCOUNT ON;

    exec dbo.GenerateRandomString 1, 1, 1, null, 30, @inviteKey OUT

    INSERT INTO [dbo].[invite_key] ([invite_key], [inviter_id], [invitee_email], [eff_datetime])
       SELECT 
          @inviteKey, @inviterId, @inviteeEmail, GETDATE()
END

I've been staring at this thing for an hour now trying to find what I've messed up, but I need another set of eyes.

UPDATE 1:

I have since changed the parameters to the following pattern:

var command = new SqlCommand("GenerateInviteKey", conn);
command.Parameters.Add(new SqlParameter("@inviterId", invite.InviterId));
command.Parameters.Add(new SqlParameter("@inviteeEmail", invite.InviteeEmail));
command.Parameters.Add(new SqlParameter("@inviteKey", ParameterDirection.Output));

and changed the procedure parameter in question to @inviterId bigint, which had the same result.

Why are you using the SqlParameter constructor that takes a source column? — Preston Guillot
Force of habit, I'm assuming by your comment that there is a more correct way of doing that. If so, could you advise a better way? — Rex_C
Try using SqlDbType.Integer instead of BigInt, and get rid of the column names, just so we don't have to think about them. — John Saunders
@Rex_C I don't know if it's related to your problem, but it's not a form I've seen often. According to MSDN it's used only for update statements, but it doesn't go into much detail. Generally though, if you don't know why the argument is being passed, and there's an overload that doesn't require it, I'd choose the simpler version. — Preston Guillot

Martin Smith Martin Smith · Accepted Answer · 2014-12-08T21:05:43

You forgot to set the CommandType to CommandType.StoredProcedure

So it just ends up passing the parameters in, then executing the procedure by name and never passing the parameters in to that call.

'Procedure or function expects parameter, which was not supplied' error

2 Answers