Passing a dataset to a SQL Server stored procedure

Question

Hello Guys,

Here I am facing a problem that I want to pass a dataset to a SQL Server stored procedure and I don't have any idea about it and there is no alternate solution (I think so ) to do that, let me tell what I want ...

I have an Excel file to be read , I read it successfully and all data form this excel work book import to a dataset. Now this data needs to be inserted into two different tables and there is too many rows in Excel workbook so it is not good if I run it from code behind that's why I want to pass this dataset to stored procedure and than ........

please suggest me some solution .

Answer 1

Not knowing what database version you're working with, here are a few hints:

• if you need to read the Excel file regularly, and split it up into two or more tables, maybe you need to use something like SQL Server Integration Services for this. With SSIS, you should be able to achieve this quite easily

• you could load the Excel file into a temporary staging table, and then read the data from that staging table inside your stored procedure. This works, but it gets a bit messy when there's a chance that multiple concurrent calls need to be handled

• if you're using SQL Server 2008 and up, you should look at table-valued parameters - you basically load the Excel file into a .NET DataSet and pass that to the stored proc as a special parameter. Works great, but wasn't available in SQL Server before the 2008 release

• since you're using SQL Server 2005 and table-valued parameters aren't available, you might want to look at Erland Sommarskog's excellent article Arrays and Lists in SQL SErver 2005 - depending on how big your data set is, one of his approaches might work for you (e.g. passing as XML which you parse/shred inside the stored proc)