SQL Server 2005: passing params to SP by reading from a file?

[moretea]

We already have stored procedures in place to update or insert to a table; what we would like to do now is to pass in an .XLS (or whatever) with the associated parameters, from which SQL Server could read row by row and determine which SP to run. Something like this...

SP      sessionID  facilityID  category  mfr          model          num_units
UPDATE  10001      100         sedan     Ford         Taurus         3
UPDATE  10003      105         sedan     Honda        Accord         -2
INSERT  10010      107         minivan   Chrysler     T&C            2
UPDATE  10015      110         pickup    Dodge        Ram            1
INSERT  10020      113         pickup    Chevrolet    Silverado      3
UPDATE  10022      117         suv       Toyota       RAV4           -1
...

We have about 1000+ rows of data to be either updated or added; surely this can be automated in this way, can't it? TIA for your help!

[wwb_99]

How does the data come in? That will really determine what your reading options are.

Anyhow, I'd probably hit it as a .NET command line application using something like filereader to load the data into a structure then running ADO.NET commands to fire the procedures. Add a little log4net action to handle logging to console/email/whatever and we be chilling.

[moretea]

Thanks for the reply! Any recommendations on good reading regarding .NET and filereader on the command line?

We're not running .NET solutions around here, although I was looking to see if FSOs could be leveraged; plus, my boss is no MS fan. I think we may end up going with the solution at http://weblogs.asp.net/jgalloway/arc...12/442618.aspx. It won't dynamically select which SP to run based on input, but that's not a dealbreaker; we'll just run it once for all the INSERT queries, and once for all the UPDATE queries. (We are handling DELETEs by just setting the number of units to 0.)

[wwb_99]

First, you are running .NET solutions--Sql 2005 is partially built on .NET 2.0.

I meant to say filehelpers, not filereaders. It can be found @ http://filehelpers.sourceforge.net/. Its a relatively easy to use library for parsing text and excel files. If you can get the data in XML you've got much better options.

Not sure how Jon's database script helps you--bigger issue is parsing (and dealing with the inevitable errors) the incoming data, not how to throw it at SQL Server.