Programmatically Retrieving a Stored Procedure's ParametersBy Scott Mitchell
Stored procedures in SQL Server are similar to methods in C# and Visual Basic code. They encapsulate one or more statements into a single, parameterized construct. Both stored procedures and methods are a form of code reuse and their use help developers adhere to the DRY principle (Don't Repeat Yourself). But the similarities don't end there. The .NET Framework has a feature called Reflection that enables developers to programmatically retrieve a list of methods for a given class, along with their input parameters and return types. It's also possible to programmatically determine what stored procedures exist in a database, along with each stored procedure's input and output parameters.
Being able to programmatically retrieve a database's stored procedures and determine their parameters are useful in a handful of scenarios. For example, code generators like CodeSmith and the Typed DataSet feature in Visual Studio use these techniques to determine the code to construct to call each stored procedure. These techniques are also useful for allowing ad-hoc stored procedure execution from a page on your website, which can be a useful tool for administrators.
This article shows how to retrieve a list of stored procedures in a database and how to enumerate a selected stored procedure's input and output parameters. We'll also look at how to let the user visiting the page pick a stored procedure, enter values for its parameters, and execute and view the resulting output. Read on to learn more!
An Overview of the Demo Application
The download available at the end of this article includes a web page with a DropDownList Web control enumerating the stored procedures in the website's database. Selecting a stored procedure form the DropDownList causes a postback. On postback the stored procedure's input and output parameters are each displayed in a BulletedList control. In addition, the input parameters are listed in a two-column GridView: the first column lists each parameter's name, while the second has a TextBox control. The user visiting the page can enter values for the input parameters into these TextBoxes and then click the "Execute Stored Procedure" Button. Doing so displays the results in another GridView further down on the page.
The following screen shot shows this demo page in action.
The DropDownList at the top of the page lists the stored procedures in the database.
In the screen shot below I have selected the
aspnet_Membership_GetAllUsers stored procedure, which is one of the stored procedures
used by the
SqlMembershipProvider. (For more information on ASP.NET's Membership feature, read Examining
ASP.NET's Membership, Roles, and Profile.)
The selected stored procedure's input and output parameters are enumerated in bulleted lists. Note that there are three input parameters for this
@PageSize. These input parameters are listed alongside TextBoxes
in a GridView further down in the page. A user can enter values for these parameters, click the "Execute Stored Procedure" button, and see the results
in a GridView at the bottom of the page. Granted, the GridView contains rather raw-looking, unformatted data, but the data is there nevertheless.
The remainder of this article looks at how various portions of this demo were implemented.
Retrieving a List of Stored Procedures
Every Microsoft SQL Server database includes a number of system tables that catalog the various objects that comprise the database, including the database's tables (and their columns), its triggers, its stored procedures, its UDFs, and so on. The "core" system table, and the one that contains a list of the database's stored procedures, is named
sysobjects. Each stored procedure is represented as a row in
sysobjects. Likewise, each table and constraint and view and so on are represented by rows in this table. The different database objects can be differentiated by the value in the
sysobjects.typecolumn - stored procedures have a
The following SQL query returns the name of every stored procedure in the database:
The DropDownList in the demo is populated by a SqlDataSource control that runs the above query. It's that simple.
Retrieving a Stored Procedure's Parameters
Once a stored procedure has been selected, how do we retrieve its parameters? While this information can be retrieved from SQL Server's system tables, there's an easier way: by calling the
DeriveParametersmethod. This method accepts a
SqlCommandobject as its input and populates the
Parameterscollection with the specified stored procedure's parameters.
The following code illustrates the
SqlCommandBuilder.DeriveParameters method in action. (Note: While this article shows Visual
Basic code, the demo available for download at the end of this article includes both a VB and C# version.)
While I don't show the code in the above snippet, the first step is to create a valid
SqlConnection object to the database (which I named
myConnection in the code above). Next create a
SqlCommand object, and associate it with the
CommandText property to the name of the stored procedure whose parameters you want and set the
CommandType property to
StoredProcedure. All that remains is to open a connection to the database and call the
passing in the
SqlCommand object. This populates the passed-in
Parameters collection with
the specified stores procedure's parameter information.
Parameters property is a collection of
SqlParameter object represents a parameter in a parameterized query or stored procedure. The most germane properties of
SqlParameter class are:
ParameterName- the name of the parameter.
SqlDbType- the parameter's data type.
Direction- indicates whether the parameter is an input parameter, is an output parameter, or can serve as both an input and output parameter. As discussed in Retrieving Scalar Data from a Stored Procedure, output parameters are useful for returning scalar values from a stored procedure.
Value- the parameter's value.
Once you have the parameters you can close the connection to the database and work with the parameters. The following code snippet continued the code from above by enumerating the returned parameters, displaying each parameter (along with its data type) in the appropriate BulletedList control depending on whether the parameter serves as an input or output parameter.
Prompting the User for Input Parameter Values and Executing the Stored Procedure With Those Values
In addition to the two BulletedList controls the demo page also includes a GridView named
gvParametersthat lists the selected stored procedure's input parameters along with a TextBox for each parameter. In particular, this GridView is programmatically bound to a list of
SqlParameterobjects (namely, the set of input parameters returned by
SqlCommandBuilder.DeriveParameters) and is composed of two fields:
- A BoundField that displays each
- A TemplateField that contains a TextBox control named
DataKeyFieldsproperty is set to
The page also includes an "Execute Stored Procedure" Button control and another GridView for the stored procedure results. This results GridView
gvResults. When the "Execute Stored Procedure" Button is clicked the following code runs:
The code starts by configuring a
SqlCommand object that will execute the specified stored procedure. In this case we don't need to
go off and programmatically determine the stored procedure's parameters because they are already encompassed by the
Each row in the
gvParameters GridView is enumerated and the parameter's name and user-entered value are grabbed and added to the
Parameters property via the
AddWithValue(name, value) method.
After the parameters have been added to the
SqlCommand object the results are bound to the
That's all there is to it!
Programmatically retrieving the list of stored procedures in a database and those stored procedures' parameters are quite easy tasks. The database provides both the stored procedures and their parameters through system tables. Additionally, the parameters for a stored procedure can also be retrieved via the .NET Framework's
The demo available at the end of this article implements a quick and dirty dynamic reporting system. In short, it allows the visitor to execute any stored procedure on the database through a single page. Granted, the results are ugly and there's nothing stopping the visitor from executing any stored procedure she chooses, but such a page, when limited to a section accessible only to administrators, might be useful. In fact, with a little more effort it is possible to enhance this functionality to create a more user-friendly and secure general purpose reporting page. A future article will explore such functionality in greater detail.