c# - 我从 C# 调用带有 2 个参数(输入和输出)的存储过程，但输出参数的值不显示

Question

我写了一个简单的程序，它调用一个带有 2 个参数(一个输入/一个输出)的存储过程，代码执行没有错误，但是输出参数的值没有显示在输出窗口中!我的存储过程:

CREATE PROCEDURE [dbo].[sp_return_orders_by_employeeid_and_show_order_count]
(
@empid int,
@ordercount int=0 output
)
as
select *
from Orders
where orders.EmployeeID=@empid;
select *
from Orders
where Orders.EmployeeID=@empid
return @ordercount

并编写了以下 C# 代码:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DB02
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection connection = new SqlConnection(@"server=.\sqlexpress;
            integrated security=true; database=northwind");
            try
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_return_orders_by_employeeid_and_show_order_count";
                SqlParameter inparam = cmd.Parameters.Add("@empid", SqlDbType.Int);
                inparam.Direction = ParameterDirection.Input;
                inparam.Value = 2;
                SqlParameter outparam = cmd.Parameters.Add("@ordercount", SqlDbType.Int);
                outparam.Direction = ParameterDirection.Output;

                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Console.WriteLine("{0}\t{1}\t{2}\t", rdr[0].ToString(), rdr[1].ToString(),
                        rdr[9].ToString());
                } 
                Console.WriteLine("Numbers of Orders= {0}", outparam.Value);          
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                connection.Close();
            }

            }
    }
}

在输出中:

“outparam”的值不显示!感谢您提供有用的答案。

Answer 1

我认为您正在使用 SQL Server(通过查看您的 C# 代码)，因此您不应该返回值，您只需要将输出变量设置为您想要返回的值，如下所示:

SET @ordercount = 10;

尝试将您的存储过程更改为:

CREATE PROCEDURE [dbo].[sp_return_orders_by_employeeid_and_show_order_count]
(
    @empid INT,
    @ordercount INT OUTPUT
)
AS BEGIN

    SELECT @ordercount = COUNT(*)
    FROM Orders
    WHERE Orders.EmployeeID = @empid;

END