Passing table valued parameter to stored proc with different number of fields

Question

I have a requirement where I need to pass a table valued parameter to a stored procedure. Clients need the flexibility to add fields to the table valued type without affecting C# code (even though it requires deleting and recreating all dependent stored procedures). Is it even possible to add fewer columns to the DataTable than what is in the data type? We are using Microsoft SQL Server 2008 R2.

I have tried this:

SQL:

CREATE TYPE [dbo].[MyDataType] As Table
(
    ID INT NULL,
    Name NVARCHAR(50) NULL
)

CREATE PROCEDURE [dbo].[MyProcedure]
(
    @myData As [dbo].[MyDataType] Readonly
)
AS
Begin
    Select * FROM @myData
End

C#:

DataTable myDataTable = new DataTable("MyDataType");
myDataTable.Columns.Add("Id", typeof(Int32));
myDataTable.Rows.Add(1);
myDataTable.Rows.Add(2);

SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@myData";
parameter.SqlDbType = System.Data.SqlDbType.Structured;
parameter.Value = myDataTable;
command.Parameters.Add(parameter);

I am getting following exception:

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
Additional information: Trying to pass a table-valued parameter with 1 column(s) where the corresponding user-defined table type requires 2 column(s).

score 2 · Answer 1 · answered Feb 24 '15 at 22:58

2

You have to pass a dataTable with a number of column as same as database table columns.

DataTable myDataTable = new DataTable("MyDataType");
myDataTable.Columns.Add("ID", typeof(Int32));
myDataTable.Columns.Add("Name ", typeof(string));

answered Feb 24 '15 at 22:58

Med.Amine.Touil

1,225
2
11
15

Thanks but it does not answer my question. – Naveed Shams Feb 24 '15 at 23:00
OK! bro . so you want a generic way that allows you to execute your procedures ?? – Med.Amine.Touil Feb 24 '15 at 23:04
I could have passed XML for that but unfortunately I cannot change the stored proc at this point of time. – Naveed Shams Feb 24 '15 at 23:07
What about doing this by two steps. I ll put it as a second answer and if it helps you that will be great. – Med.Amine.Touil Feb 24 '15 at 23:10

score 1 · Accepted Answer · answered Feb 25 '15 at 01:15

I ended up generating data table dynamically as follows:

   public static DataTable GetDataTableFromSchema(string userDefinedTableTypeName, SqlConnection connection)
    {
        var query = "SELECT SC.name, ST.name AS datatype FROM sys.columns SC " +
                    "INNER JOIN sys.types ST ON ST.system_type_id = SC.system_type_id AND ST.is_user_defined = 0 " +
                    "WHERE SC.object_id = " +
                    "   (SELECT type_table_object_id FROM sys.table_types WHERE name = '" + userDefinedTableTypeName + "');";

        var dataTable = new DataTable();

        using (var command = new SqlCommand(query, connection))
        {
            using (var sqlDataReader = command.ExecuteReader())
            {
                while (sqlDataReader.Read())
                {
                    var columnName = sqlDataReader["name"].ToString();
                    var sqlDbType = (SqlDbType) Enum.Parse(typeof (SqlDbType), sqlDataReader["datatype"].ToString(), true);
                    var clrType = GetClrType(sqlDbType);
                    dataTable.Columns.Add(columnName, clrType);
                }
            }                
        }


        return dataTable;
    }

I am getting Error on var clrType = GetClrType(sqlDbType); getclrtype does not exisit in the current context — sushil.agarwal, Nov 16 '20 at 18:20
Plz Check Link to find GetClrType() function https://stackoverflow.com/questions/1058322/anybody-got-a-c-sharp-function-that-maps-the-sql-datatype-of-a-column-to-its-clr — Ghotekar Rahul, Dec 30 '20 at 08:56

score 0 · Answer 3 · answered Feb 24 '15 at 23:18

Step One is about executing a query to get columns' Name and Type by quering your dataBase.INFORMATION_SCHEMA.

SELECT COLUMN_NAME,DATA_TYPE 
FROM yourDataBase.INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = 'yourTableName'

Step Two is to use This result in a loop statement to build your required dataTable that you'll send it as a parameter

Foreach (var item in ColumnsNameType)
{
   myDataTable.Columns.Add(item.COLUMN_NAME, typeof(item.COLUMN_NAME));
}

score 0 · Answer 4 · answered Apr 13 '19 at 09:09

0

First get the structure of the datatable (your table type)

DECLARE @ret yourTableName
SELECT * FROM @ret

Then in C# fill it with your data and use it for calling your stored procedure

answered Apr 13 '19 at 09:09

DanCZ

187
4
10

Passing table valued parameter to stored proc with different number of fields

4 Answers4