How To Call a Stored Procedure with an Output using C#

Question

I have this SQL Server stored procedure that provides an output value. Note the output value is the identity seed that gets created when the new record is added.

    @EventNumber varchar(12),
    @inc_date_occurred varchar(10),
    @inc_time_occurred int,
    @inc_location varchar (200),
    @inc_location_exempt bit,
    @inc_case_number varchar(9),
    @Zone varchar(50),
    @inc_school_name varchar(50),
    @inc_special_detail_name varchar(50),
    @Inc_ID int output
AS
BEGIN
    IF @EventNumber IS NOT NULL
       --Start Building a Record that comes in with an Event Number
       INSERT INTO Tbl_Incident(inc_event_number, inc_is_uof, inc_is_comp_via_sup,
                                inc_is_comp_via_psd, inc_date_recvd, inc_date_occurred_start,
                                inc_time_occurred_start, inc_location, inc_location_exempt,
                                inc_case_number, inc_status, inc_comp_complaint_to_psd,
                                inc_synopsis, inc_zone, inc_school_name,
                                inc_special_detail_name, inc_arrest_made, 
                                inc_date_entered, inc_entered_by)

      VALUES (@EventNumber, '1', --inc_is_uof,
              '0', --inc_is_comp_via_sup
              '0', --inc_is_comp_via_sup
              GETDATE(),--inc_date-received,
              CAST(@inc_date_occurred AS DATE),--inc_date_occurred
              --start time conversion
              right(convert(varchar(20), cast(stuff(right('0000' + convert(varchar(4),@inc_time_occurred),4),3,0,':') as datetime),100),7),
              -- end time converted from Military to standard
              @inc_location, @inc_location_exempt, @inc_case_number,
              'OPEN',--@inc_status,
              '0', --
              'This is a for REVIEW ONLY',--synopsis,
              @Zone, @inc_school_name, @inc_special_detail_name,
              '0', --@inc_arrest_made
              GETDATE(), --inc_date_entered
              'SharePoint Transfer'--@inc_entered_by
              )

    SET @Inc_ID = @@IDENTITY

I am using this C# code to call this procedure with the necessary parameters and would like to get the output integer back.

using (SqlCommand _Incident = new SqlCommand("ocso_InsertIncident", _con))
{
    _Incident.CommandType = CommandType.StoredProcedure;

    // set up parameters
    _Incident.Parameters.AddWithValue("@EventNumber", EventNumber);
    _Incident.Parameters.AddWithValue(@"inc_Date_Occurred", DateTime.Parse("01-10-2017"));
    _Incident.Parameters.AddWithValue("@inc_Time_Occurred", "1535");
    _Incident.Parameters.AddWithValue("@inc_Location", "Web Service Entry");
    _Incident.Parameters.AddWithValue("@inc_location_exempt", 0);
    _Incident.Parameters.AddWithValue("@inc_Case_Number", "2107-123456");
    _Incident.Parameters.AddWithValue("@Zone", "34");
    _Incident.Parameters.AddWithValue("@inc_school_name", DBNull.Value);
    _Incident.Parameters.AddWithValue("@inc_special_detail_name", DBNull.Value);

    // Output value
    SqlParameter inc_ID = new SqlParameter("@ID", DbType.Int16);
    inc_ID.Direction = System.Data.ParameterDirection.Output;
    _Incident.Parameters.Add(inc_ID);

    _Incident.ExecuteNonQuery();

    string inc_id = _cmd.Parameters["@ID"].Value.ToString();
}

The code builds but when I run it I am getting this error message when the execution of the stored procedure is called

{"Procedure or function 'ocso_InsertIncident' expects parameter '@Inc_ID', which was not supplied."}

So I tried passing a null value for the @INC_ID Paramter by including this in the code

_Incident.Parameters.AddWithValue("@INC_ID", DBNull.Value);

but I then get the error message {"Procedure or function ocso_InsertIncident has too many arguments specified."}

I seem to be stuck can someone help out please

Thanks

FYI DbType.Int16 is for a smallint but @Inc_ID is an int, use SqlDbType.Int — Alex K.
– Alex K., Commented Jan 23, 2017 at 14:56
You really should use SCOPE_IDENTITY here instead. If there is a trigger on that table that does an insert with an identity you will get that value instead of the one from the table you are inserting in to. blog.sqlauthority.com/2007/03/25/… — Sean Lange
– Sean Lange, Commented Jan 23, 2017 at 15:04

kgzdev · Accepted Answer · 2017-01-23 14:51:21Z

2

Remove this code

_Incident.Parameters.AddWithValue("@INC_ID", DBNull.Value);

and modify your codes like this

SqlParameter inc_ID = new SqlParameter("@INC_ID", DbType.Int16);
                    inc_ID.Direction = System.Data.ParameterDirection.Output;
                    _Incident.Parameters.Add(inc_ID);
                    _Incident.ExecuteNonQuery();

                    string inc_id = _cmd.Parameters["@INC_ID"].Value.ToString();

answered Jan 23, 2017 at 14:51

kgzdev

2,9312 gold badges21 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Perry Over a year ago

Thanks for the quick response your suggestion worked. Now moving on to another issue getting an error on the parsing the date

Bruno Guardia · Accepted Answer · 2017-01-23 14:52:35Z

2

You don't need to provide a value for the parameter, and should indicate it is an output parameter. Instead of:

_Incident.Parameters.AddWithValue("@INC_ID", DBNull.Value);

Use:

SqlParameter outputValue = new SqlParameter("@INC_ID", SqlDbType.Int);
outputValue.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outputValue);

answered Jan 23, 2017 at 14:52

Bruno Guardia

4724 silver badges15 bronze badges

Comments

Onkel Toob · Accepted Answer · 2017-01-23 14:50:40Z

1

You provide a parameter called "ID" that is not needed by the procedure:

SqlParameter inc_ID = new SqlParameter("@ID", DbType.Int16);
inc_ID.Direction = System.Data.ParameterDirection.Output;
_Incident.Parameters.Add(inc_ID);

Try renaming this parameter to "@Inc_ID":

SqlParameter inc_ID = new SqlParameter("@Inc_ID", DbType.Int16);
inc_ID.Direction = System.Data.ParameterDirection.Output;
_Incident.Parameters.Add(inc_ID);

answered Jan 23, 2017 at 14:50

Onkel Toob

2,2222 gold badges18 silver badges27 bronze badges

Collectives™ on Stack Overflow

How To Call a Stored Procedure with an Output using C#

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related