如何从c#函数的存储过程中返回多个输出参数

I'm using output parameters to get values from my database.

我使用输出参数从数据库中获取值。

This is my stored procedure:

这是我的存储过程:

ALTER PROCEDURE [dbo].[sp_GetCustomerMainData] 
    -- Add the parameters for the stored procedure here
        @Reference nvarchar(100),
        @SubscriptionPIN nvarchar(100) OUTPUT,
        @SignupDate nvarchar(100) OUTPUT,
        @ProductCount int OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SET @SubscriptionPIN = 'N/A'
    SET @SignupDate = 'N/A'
    SET @ProductCount = 0

    -- Insert statements for procedure here
    IF EXISTS(SELECT [SubscriptionPIN] FROM [Norton].[dbo].[Customers] WHERE [Reference] = @Reference)
    BEGIN
        SELECT TOP 1 @SubscriptionPIN = [SubscriptionPIN], @SignupDate = SignUpDate  FROM [Norton].[dbo].[ProductList] WHERE [Reference] = @Reference
        SET @ProductCount = (SELECT COUNT(*) FROM [Norton].[dbo].[ProductList] WHERE [Reference] = @Reference)
    END

    RETURN (@SubscriptionPIN)
    RETURN (@SignupDate)
    RETURN (@ProductCount)
END

I'm not sure about the returns at the end:

我不确定最后的回报:

RETURN (@SubscriptionPIN)
RETURN (@SignupDate)
RETURN (@ProductCount)

On the other side, here is the c# code :

另一方面，这里是c#代码:

using (SqlConnection con = new SqlConnection(connectionInfo))
{
    using (SqlCommand cmd = new SqlCommand("sp_GetCustomerMainData", con) { CommandType = CommandType.StoredProcedure })
    {
        cmd.Parameters.Add("@Reference", SqlDbType.NVarChar).Value = CustomerReferenceID;

        SqlParameter SubscriptionPIN = new SqlParameter("@TheCustomerID", SqlDbType.NVarChar) { Direction = ParameterDirection.Output };
        cmd.Parameters.Add(SubscriptionPIN);

        SqlParameter SignupDate = new SqlParameter("@SignupDate", SqlDbType.NVarChar) { Direction = ParameterDirection.Output };
        cmd.Parameters.Add(SignupDate);

        SqlParameter ProductCount = new SqlParameter("@ProductCount", SqlDbType.Int) { Direction = ParameterDirection.Output };
        cmd.Parameters.Add(ProductCount);

        con.Open();

        try
        {
            cmd.ExecuteNonQuery();

            if (cmd.Parameters["@TheCustomerID"].Value.ToString() != "N/A")
            {
                aStatus.SubscriptionPIN = cmd.Parameters["@TheCustomerID"].Value.ToString();
                aStatus.SignupDate = cmd.Parameters["@SignupDate"].Value.ToString();
                aStatus.ProductCount = int.Parse(cmd.Parameters["@ProductCount"].Value.ToString());
                aStatus.Result = "0: Reference ID Found";
             }
             else
             {
                 aStatus.Result = "1: Reference ID does not exists";
                 return aStatus;
             }
          }
          catch (SqlException sqlExc)
          {
              foreach (SqlError error in sqlExc.Errors)
              {
                  aStatus.Result = string.Format("{0}: {1}", error.Number, error.Message);
                  return aStatus;
              }
          }
      }
}

When I run this code, I get error:

当我运行这个代码时，我得到错误:

System.InvalidOperationException: String[1]: the Size property has an invalid size of 0.
at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
at System.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection parameters)
at System.Data.SqlClient.SqlCommand.BuildRPC(Boolean inSchema, SqlParameterCollection parameters, _SqlRPC& rpc)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

系统。InvalidOperationException: String[1]: Size属性的大小为0。在System.Data.SqlClient.SqlParameter。在system . data . data . sqlclient . sqlcommand验证(Int32 index, Boolean isCommandProc)。SetUpRPCParameters(_SqlRPC、Int32 startCount、Boolean inSchema、SqlParameterCollection参数)位于system . data . data . sqlclient . sqlcommand。在System.Data.SqlClient.SqlCommand上构建drpc (Boolean inSchema, SqlParameterCollection参数，_sqlrpc&rpc)。RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior RunBehavior RunBehavior RunBehavior RunBehavior, Boolean returnStream, Boolean async)位于system . data . data . sqlclient . sqlcommand。RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior RunBehavior RunBehavior, Boolean returnStream, String方法，DbAsyncResult result)位于system . data . data . sqlclient . sqlcommand。InternalExecuteNonQuery(DbAsyncResult结果，String methodName, Boolean sendToPipe)位于system . data . data . sqlclient . sqlcommand.executenonquery ()

I don't know what is the correct way to send many output parameters from stored procedure, can someone help please?

我不知道从存储过程中发送许多输出参数的正确方法是什么，有人能帮忙吗?

5 个解决方案

#1

You need to specify the maximum length for the nvarchar parameters:

您需要指定nvarchar参数的最大长度:

SqlParameter SubscriptionPIN = new SqlParameter("@TheCustomerID", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output };
cmd.Parameters.Add(SubscriptionPIN);
SqlParameter SignupDate = new SqlParameter("@SignupDate", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output };
cmd.Parameters.Add(SignupDate);

Remove the return statements from the stored procedure. You don't need to do anything for the output parameters to be returned. (Also, you can only use one return statement, and you can only return integer values. You would use a parameter with the direction ReturnValue to get that returned value.)

从存储过程中删除return语句。您不需要为返回的输出参数做任何事情。(此外，您只能使用一个return语句，并且只能返回整数值。您将使用带有方向ReturnValue的参数来获取返回值)

#2

Procedure execution ends after your first RETURN which "Exits unconditionally from a query or procedure."

过程执行在第一次返回后结束，该返回“无条件地从查询或过程中退出”。

Consider returning both values as one recordset with

考虑返回两个值作为一个记录集

SELECT @SubscriptionPIN AS SubsPIN , @SignupDate AS SignUpDate, @ProductCount AS ProdCount

at the end of the procedure.

在程序的最后。

#3

>Try this its working fine for the multiple output parameter:

>对多个输出参数进行测试，效果良好:

using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["conStringEndicia"].ConnectionString)){

                using (var sqlCmd = new SqlCommand("endicia.credentialLookup", sqlConnection))
                { 

                    sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlCmd.Parameters.AddWithValue("@accountNumber", accountNumber);
                    SqlParameter outLogin = new SqlParameter("@login", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output };
                    sqlCmd.Parameters.Add(outLogin);
                    SqlParameter outPassword = new SqlParameter("@password", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output };
                    sqlCmd.Parameters.Add(outPassword);
                    sqlConnection.Open();
                    sqlCmd.ExecuteNonQuery();
                    string login, password;
                    login = outLogin.Value.ToString();
                    password = outPassword.Value.ToString();                        
                }
            }

#4

Here is what I tried and it is working fine

这是我试过的，效果很好

**Stored Procedures**

STORED PROCEDURE 1

create procedure spLoginCount
@userid nvarchar(50),
@password nvarchar(50),
@count int out
as 
Begin 
    select @count=count(userid) from users where userid=@userid and pswd=@password
End



**STORED PROCEDURE 2**

create procedure spLoginData
@userid nvarchar(50),
@usertype nvarchar(20) out,
@lastlogin nvarchar(100) out
as 
Begin 
    select @usertype=usertype,@lastlogin=lastlogin from users where userid=@userid
End


**ASP.NET code which will get values of two output Parameters**....



 protected void btnLogin_Click(object sender, EventArgs e)
    {
        string uid="", psw="";
        uid = txtUserName.Text;
        psw = txtPassword.Text;

         string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
         using (SqlConnection scon = new SqlConnection(cs))
         {
             SqlCommand scmd = new SqlCommand("spLoginCount", scon);
             scmd.CommandType = System.Data.CommandType.StoredProcedure;
             scmd.Parameters.AddWithValue("@userid",uid);
             scmd.Parameters.AddWithValue("@password", psw);

             SqlParameter outparameter = new SqlParameter();
             outparameter.ParameterName = "@count";
             outparameter.SqlDbType = System.Data.SqlDbType.Int;
             outparameter.Direction = System.Data.ParameterDirection.Output;
             scmd.Parameters.Add(outparameter);

             scon.Open();
             scmd.ExecuteScalar();

             string count = outparameter.Value.ToString();
             if (count == "1")
             {
                 SqlCommand scmd1= new SqlCommand("spLoginData", scon);
                 scmd1.CommandType = System.Data.CommandType.StoredProcedure;
                 scmd1.Parameters.AddWithValue("@userid", uid);

                 /*SqlParameter outUserType = new SqlParameter();
                 outUserType.ParameterName = "@usertype";
                 outUserType.SqlDbType = System.Data.SqlDbType.NText;
                 outUserType.Direction = System.Data.ParameterDirection.Output;
                 scmd1.Parameters.Add(outUserType);
                 */
                 SqlParameter outUserType = new SqlParameter("@usertype", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output };
                 scmd1.Parameters.Add(outUserType);

                 SqlParameter outLastLogin = new SqlParameter("@lastlogin", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output };
                 scmd1.Parameters.Add(outLastLogin);

                 scmd1.ExecuteNonQuery();
                 scon.Close();

                 string usertype,lastlogin;
                 usertype = outUserType.Value.ToString();
                 lastlogin = outLastLogin.Value.ToString();
               }

             }
         }

#5

SqlParameter SubscriptionPIN = new SqlParameter("@TheCustomerID", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output }; cmd.Parameters.Add(SubscriptionPIN); SqlParameter SignupDate = new SqlParameter("@SignupDate", DbType.String, 100) { Direction = ParameterDirection.Output }; cmd.Parameters.Add(SignupDate);

SqlParameter SubscriptionPIN = new SqlParameter(“@TheCustomerID”，SqlDbType。NVarChar, 100) {Direction =参数方向。输出};cmd.Parameters.Add(SubscriptionPIN);SqlParameter SignupDate = new SqlParameter(“@SignupDate”，DbType)。字符串，100){方向=参数方向。输出};cmd.Parameters.Add(SignupDate);

must give type and size

必须给出类型和尺寸吗

#1