One of the coolest things added to Microsoft SQL Server in 2008 was the ability to pass sets to stored procedures as input parameters. This was enabled by creating user defined table types in the database. Then you could pass instances of that user defined table as an input parameter to a stored procedure.
There is another method of passing sets to a stored procedure that worked with earlier versions. This method continues to work today, and may be preferable in some cased. You can pass data to a stored procedure by using a # temp table. If you create a # temp table in an SQL Statement, or an SQL Statement embedded in a stored procedure, you can then call another stored procedure, and have the # temp table available to it. That is because the scope and lifetime of a # temp table is the database connection, or process.
Procedure A creates temp table #T. Procedure A then calls Procedure B. The table #T is available in Procedure B as if it was created within procedure B.
Here as some advantages of this method:
- Using a temp table allows the data to be used across databases. Using a user defined table object only works within the database in which it is created.
- If you use a user defined table object, and need to modify it, you have to remove all references to it in stored procedures, functions, etc. before changes can be made. Because a # table is not truly defined until runtime, it can be modified and the stored procedures using it without having to be concerned about the dependencies. This ca be a benefit or a bane.
- Using a # table allows you to populate it from your client application using Bulk Copy. This can increase performance when you are sending large sets of data.
From a client application using ADO, you can have a composite SQL query that creates a temp table, and then calls a stored procedure using the temp table. That becomes the SQL command for your command object. Then, define a complex data type parameter for you command to populate the stored procedure using ADO Bulk Copy. This bulk copy works exactly the same as examples to import data into permanent tables. Now you call Exec Non-Query against the ADO command. The temp table is populated through the Bulk Copy. Then the stored procedure is called, and can act upon the data stored in the temp table.
Consider this option when you have large amounts of data to send, or you do not have a clearly defined table definition to pass, like when you are in early stages of development.
Cheers,
Ben