Take the 2-minute tour ×

As it was discussed here: Passing List<> to SQL Stored Procedure and in many other places, one way to pass table as parameter is to use SqlParameter.

The problem with this is that it requires to specify User Defined Table Type in TypeName property.

I need to load data from an Excel file and pass it as parameter to SQL Statement which will join this data with other database tables to get the result.

In order to do that I would need to create User Defined Table Type for each possible variation of Excel file. I don't want to pollute SQL types with those types that are used just to pass data from each Excel file. How do I achieve that ?

You may ask how can I join something that I don't know type of - the answer is - I know names and types of some fields, but not all. Also if I want to return joined data using select * it would work with any list of fields without me hardcoding all of them inside type.

I can't use OPENROWSET to join this file directly from SQL because I have 64 bit SQL Server and it does not work giving me strange error.

There is concept of temporary tables in SQL Server, but there is no concept of temporary types, how do I simulate temporary type or avoid using it when passing table ?

I could pass data as XML, but it seems less efficient if data size is large enough plus data size is limited to 2Gb.

One way to organize it is to create type, then execute statement, then drop type after or use TRY..CATCH and drop type inside catch.

Another is make special schema, generate unique type names into that schema and clean it from time to time using garbage collection SQL job. And it can be used in combination with first way just to clean leftovers.

Any other suggestions ?

share|improve this question
"I could pass data as XML, but it seems less efficient if data size is large enough plus data size is limited to 2Gb." -- Having Excel file generating XML of 2GB might be a problem by itself ;-) –  milivojeviCH Dec 27 '12 at 17:10

1 Answer 1

You most certainly should not build a 2GB table variable or user-definted data type and pass that. Instead, use SSIS to pass the file into some sort of persisted table or temporary table, then manipulate it with stored procedures. Even if you managed to get 2GB of data to a stored procedure, the transaction will run for so long that it will almost certainly block other transactions.

This is as good of an answer as you can hope for without providing additional details about your scenario.

share|improve this answer

Your Answer


