How to pass a table from frontend to a stored procedure?

Question

I want to pass a table as a parameter to a stored procedure. Is it possible to do it. Please give some example.

Answer 1

Solution to this

Convert you datable in xml by using datatable.Writexml

than pass this xml as string in stored procedure parmeter with the datatype Varchar(max)

Than use OpenXml to create datatable again form the xml string passed by you.

See the example : http://msdn.microsoft.com/en-us/library/ms186918.aspx

Answer 2

In SQL Server 2008 you can use Table Valued Parameters - pass in a TABLE type as a parameter to a sproc.

I wrote up an article on the differences of the various approaches you could take, with a performance comparison here: CSV vs XML vs Table Valued Parameter