OdbcCommand on Stored Procedure - "Parameter not supplied" error on Output parameter

Question

I'm trying to execute a stored procedure (against SQL Server 2005 through the ODBC driver) and I recieve the following error:

Procedure or Function 'GetNodeID' expects parameter '@ID', which was not supplied.

@ID is the OUTPUT parameter for my procedure, there is an input @machine which is specified and is set to null in the stored procedure:

ALTER PROCEDURE [dbo].[GetNodeID] 
@machine nvarchar(32) = null,
@ID int OUTPUT
AS
BEGIN
SET NOCOUNT ON;

IF EXISTS(SELECT * FROM Nodes WHERE NodeName=@machine)
BEGIN
    SELECT @ID = (SELECT NodeID FROM Nodes WHERE NodeName=@machine)
END
ELSE
BEGIN
    INSERT INTO Nodes (NodeName) VALUES (@machine)
    SELECT @ID = (SELECT NodeID FROM Nodes WHERE NodeName=@machine)
END
END

The following is the code I'm using to set the parameters and call the procedure:

        OdbcCommand Cmd = new OdbcCommand("GetNodeID", _Connection);
        Cmd.CommandType = CommandType.StoredProcedure;

        Cmd.Parameters.Add("@machine", OdbcType.NVarChar);
        Cmd.Parameters["@machine"].Value = Environment.MachineName.ToLower();

        Cmd.Parameters.Add("@ID", OdbcType.Int);
        Cmd.Parameters["@ID"].Direction = ParameterDirection.Output;

        Cmd.ExecuteNonQuery();
        _NodeID = (int)Cmd.Parameters["@Count"].Value;

I've also tried using Cmd.ExecuteScalar with no success. If I break before I execute the command, I can see that @machine has a value.

If I execute the procedure directly from Management Studio, it works correctly.

Any thoughts? Thanks

Answer 1

I'm not exactly sure what you mean by

there is an input @machine which is specified and is set to null in the stored procedure

In your proc's signature, this line:

@machine nvarchar(32) = null

doesn't mean that you're setting @machine to null inside the proc - it means you're assigning a default value to be used in case the parameter is missing (in this case, null is the value to be used for a missing param).

Getting the error about @ID being missing would happen if you were calling this stored procedure without passing any parameters at all (@machine would not be flagged as a problem since it has a default value defined). Your code example looks fine to me - are you sure the stored proc isn't being called from somewhere else in your program (somewhere where no parameters are being added)?

Answer 2

Try replacing :

OdbcCommand Cmd = new OdbcCommand("GetNodeID", _Connection);
Cmd.CommandType = CommandType.StoredProcedure;

With :

OdbcCommand Cmd = new OdbcCommand("{call GetNodeID(?,?)}", _Connection);

More info :

http://support.microsoft.com/kb/310130