StrataFrame Forum

Question about executing stored procedures dynamically...

http://forum.strataframe.net/Topic15484.aspx

By StarkMike - 4/9/2008

Consider that I have a table like this:



ColumnName: Stored Procedure Name

InsertRecord

DeleteRecord

UpdateRecord



What I want to be able to do is iterate through the table and execute the stored procedures at run time. The stored procedures though have a different number of parameters. InsertRecord has only one parameter, while DeleteRecord has two, etc.



How could I do this in code? Obviously I dont want to have to hard code the parameters in the VB code.



Should I use the DeriveParameters command? What would be the best way to execute these stored procedures when the parameters are unknown?



Thanks :-D
By Trent L. Taylor - 4/9/2008

Well, like anything else, this can be done but the reason may change the approach.  You are going to need to know the number of parameters and the sprocs.  You can always query server for the sprocs and create a class that parses this information.  You would probably want to look at the sysobjects table.  It would be something like this (I'm just shootin from the hip here, so you may need to play with this a bit):

Select * from sysobjects where type = 'P' and category = 0

You can then start digging a little deeper to get the parms, etc. and build a logical command with the parameters, etc.

By StarkMike - 4/10/2008

Thanks for the info Trent. I found something called SQLCommandBuilder.DeriveParameters last night and I'm going to look into it... might be what I need. ;-D Just thought I would post this in case it helps anyone else or someone else has used it and can confirm its what I need.
By Trent L. Taylor - 4/10/2008

The SQLCommandBuilder is good and it may get you want you want.  That is also the very purpose of the DerivedParmameters...this class just has some limitations and can produce unoptimized queries...so that is the only thing I would caution about.