Passing multiple tables to stored procedure with multiple table-valued parameters problem

Question

I am trying to pass DataTables to a stored procedure that accepts multiple table-valued parameters. The problem is, it seems that all the parameters are being passed to the first one so I am getting errors like this:

Trying to pass a table-valued parameter with 13 column(s) where the corresponding user-defined table type requires 17 column(s).

Here's my code..

Stored Procedure header:

CREATE PROCEDURE TestProc
@Parm1 Temp1TableType READONLY,
@Parm2 Temp2TableType READONLY,
@Parm3 Temp3TableType READONLY,
@Parm4 Temp4TableType READONLY,
@Parm5 Temp5TableType READONLY

@Parm1 has 17 columns, @Parm2 13 columns, @Parm3 3 columns, @Parm4 11 columns and @Parm5 has 8 columns.

C# code (I use Microsoft.ApplicationBlocks)

DataTable dtTable1 = dsSource.Tables[0];
DataTable dtTable2 = dsSource.Tables[1];
DataTable dtTable3 = dsSource.Tables[2];
DataTable dtTable4 = dsSource.Tables[3];
DataTable dtTable5 = dsSource.Tables[4];

SqlParameter param1 = new SqlParameter("@Parm1", dtTable1);
SqlParameter param2 = new SqlParameter("@Parm2", dtTable2);
SqlParameter param3 = new SqlParameter("@Parm3", dtTable3);
SqlParameter param4 = new SqlParameter("@Parm4", dtTable4);
SqlParameter param5 = new SqlParameter("@Parm5", dtTable5);

param1.SqlDbType = SqlDbType.Structured;
param2.SqlDbType = SqlDbType.Structured;
param3.SqlDbType = SqlDbType.Structured;
param4.SqlDbType = SqlDbType.Structured;
param5.SqlDbType = SqlDbType.Structured;

SqlParameter[] parms = new SqlParameter[5];
parms[0] = param1;
parms[1] = param2;
parms[2] = param3;
parms[3] = param4;
parms[4] = param5;

returnVal = SqlHelper.ExecuteScalar(CfgKeys.ConnString, CommandType.StoredProcedure, "TestProc", parms);

Today I tried using SqlClient instead of Microsoft.ApplicationBlocks but I still got the same error.

SqlConnection conn = new System.Data.SqlClient.SqlConnection(CfgKeys.ConnString);
conn.Open();
using (conn) {
   SqlCommand cmdTestDataToServer= new SqlCommand("TestProc", conn);
   cmdTestDataToServer.CommandType = CommandType.StoredProcedure;

   SqlParameter param1 = new SqlParameter("@Parm1", dtTable1);
   SqlParameter param2 = new SqlParameter("@Parm2", dtTable2);
   SqlParameter param3 = new SqlParameter("@Parm3", dtTable3);
   SqlParameter param4 = new SqlParameter("@Parm4", dtTable4);
   SqlParameter param5 = new SqlParameter("@Parm5", dtTable5);

   param1.SqlDbType = SqlDbType.Structured;
   param2.SqlDbType = SqlDbType.Structured;
   param3.SqlDbType = SqlDbType.Structured;
   param4.SqlDbType = SqlDbType.Structured;
   param5.SqlDbType = SqlDbType.Structured;

   cmdTestDataToServer.Parameters.Add(param1);
   cmdTestDataToServer.Parameters.Add(param2);
   cmdTestDataToServer.Parameters.Add(param3);
   cmdTestDataToServer.Parameters.Add(param4);
   cmdTestDataToServer.Parameters.Add(param5);

   returnVal = cmdTestDataToServer.ExecuteScalar();
}

conn.Close();

When I run this, it seems that all the parameters are being passed to @Param1 hence the error I mentioned earlier. It works when I manually run the procedure in TSQL, so the error is on my code. Can anyone spot where I went wrong?

TIA!

Jr G. · Accepted Answer · 2010-12-01 14:09:38Z

1

Found the problem.. Forgot to mention that DataTable data is sent through a web service, so it was using the DataSet method GetXml. The problem was that the GetXml method does not include NULL columns hence the missing columns.

Thanks guys!

answered Dec 1, 2010 at 14:09

Jr G.

872 silver badges9 bronze badges

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

Collectives™ on Stack Overflow

Passing multiple tables to stored procedure with multiple table-valued parameters problem

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related