How to return multiple output parameters from stored procedure for C# function

Question

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 :

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()

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

Solution 1

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

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.)

Solution 2

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();
               }

             }
         }

Solution 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();                        
                }
            }