StrataFrame Forum » .NET Forums » General .NET Discussion » Question about executing stored procedures dynamically...

Question about executing stored procedures dynamically...

StarkMike — Thu, 10 Apr 2008 08:02:10 GMT

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

RE: Question about executing stored procedures dynamically...

Trent L. Taylor — Thu, 10 Apr 2008 08:02:10 GMT

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.

RE: Question about executing stored procedures dynamically...

StarkMike — Thu, 10 Apr 2008 06:54:12 GMT

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.

RE: Question about executing stored procedures dynamically...

Trent L. Taylor — Wed, 09 Apr 2008 22:20:31 GMT

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):

[codesnippet]Select * from sysobjects where type = 'P' and category = 0[/codesnippet]

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