2

I'm having problem with output Parameters in Microsoft Sequel Server 2008 with Enterprise Library 5.0

I'm calling a procedure from my code (in C#) and it worked fine until I added an optional output parameter to the stored proc to track feedback.

The Code for calling the proc from my code:

string insertCommand = insertParams[0]; //the stored proc command
Object[] parameterValues = insertParams.Skip(1).ToArray(); //The parameters
int feedback = 1;
IDataReader reader = localDB.ExecuteReader(insertCommand, parameterValues); //ERROR
if (reader.Eead()){
  //stuf

During debug parameterValues contains one object. The error I get is

[System.InvalidOperationException] = {"The number of parameters does not match number of values for stored procedure."}

My stored proc looks like this:

ALTER PROCEDURE [dbo].[BS_GetDetails] 
@BusStop_ID     INT,
@FEEDBACK_CODE      INT=NULL OUTPUT
AS
BEGIN
SET NOCOUNT ON;

SELECT @FEEDBACK_CODE = 200
    //Do lots of other non-related things, mostly select statements

When I call the stored proc from inside the Database with only one parameter it works fine, but as I get the error in my code after I added the extra optional output param

I could create a second stored proc to call the first, but that seems like a hacky solution and I suspect I'm missing something subtle here...

Cœur
  • 37,241
  • 25
  • 195
  • 267
Oofpez
  • 514
  • 1
  • 7
  • 18
  • It seems like ExecuteReader is overly strict and doesn't cater properly for optional paramters. using a more lengthy approach with ExecuteNonQuery and manually inserting parameters worked. – Oofpez Apr 02 '12 at 14:12

1 Answers1

0

Have you looked at your parameterValues object b/f you execute call? Ive done optional parameters with only the key/value pair that I need.

cmd.Parameters.Add( new SqlParameter("@BusStopID", myBusStopID));

g williams
  • 184
  • 1
  • 7