Sample code is available for this article - Download here.

If you’re writing SQL Server database applications (or Oracle or DB2 for that matter), you spend a lot of time building and testing stored procedures, especially those stored procedures that pass and return information through parameters rather than row sets. No matter how much you test these types of stored procedures using database tools, invariably when you plug in to your program, it doesn’t quite work the way you'd like.

Also when developing these stored procedures using database tools, you might find yourself writing a lot of T-SQL code to declare the parameters before the call and display the results afterward. Rather than writing all of that code, it would be useful to have a utility that could gather information about a stored procedure, allowing you to specify values for each parameter and run the stored procedure without having to write any code.

In this article, you’ll learn how to extract a list of stored procedures from a database, plus information about the parameters associated with the stored procedure. Finally, you’ll see how to take this information and use it to call a stored procedure on the fly.

A word of caution is in order. Since the sample program will allow a user to execute a stored procedure on the fly, it can be a big security problem. For this reason, the program prompts the user for database authentication information and doesn’t rely on information kept in a web.config file or embedded in the code. However, you should exercise extreme caution when using this program. It really shouldn’t be used against a production database unless you can guarantee that only authorized users may execute it.

In order to execute a stored procedure on the fly, you need to collect a list of stored procedures. This program uses the system stored procedure sp_stored_procedures to collect a list of stored procedures that can be executed by the user, using connection information specified by the user. Then it binds the list to a DropDownList control so that the user can choose which stored procedure to execute (see FIGURE 1).

FIGURE 1: After collecting the connection information from the TextBox controls, pressing the Login button will extract the set of stored procedures that the user can execute.

Clicking the Login button fires the LoginButton_Click event (see FIGURE 2). This routine begins by constructing a new SqlConnection object using the connection information supplied by the user on the web form. Then it creates a new SqlCommand object using the the SqlConnection object and referencing the sp_stored_procedures stored procedure. Next a new SqlDataAdapter is created using the SqlCommand object.

FIGURE 2: The LoginButton_Click event is fired when the user logs in to the database server to populate a DropDownList with the names of the stored procedures the user can execute.

In the main body of the routine, a Try statement is used to trap any database errors. Any error messages will be displayed in the Status text box. Within the Try statement, the Status text box is first cleared, and then the SqlDataAdapter is used to populate a DataSet object with the results of the sp_stored_procedures stored procedure. Finally, the DataTable object returned by the data adapter is bound to the SPs DropDownList control.

While the sp_stored_procedures stored procedure returns a number of other columns, most of the information is reserved for future use. The only columns that contain useful information besides the PROCEDURE_NAME column are the PROCEDURE_QUALIFIER, which contains the name of the database, and the PROCEDURE_OWNER, which contains the owner name associated with the procedure.

The sp_stored_procedures stored procedure also contains three optional parameters that allow you to filter results by stored procedure name, owner, and qualifier. These parameters also support wildcards, so it would be possible to retrieve all stored procedures beginning with sp_, by specifying sp_%. Since the default is to retrieve everything, the call to sp_stored_procedures in this routine doesn't require any parameters to be specified.

Once the list of stored procedures is retrieved from the database, the user can select a stored procedure name from the drop-down list and click the Get Parameters button to build a DataGrid containing information about its parameters (see FIGURE 3).

FIGURE 3: After selecting a stored procedure from the drop-down list, click the Get Parameters button to retrieve and format information about each parameter associated with the stored procedure.

Rather than using the normal DataGrid editing tools, this program uses a template column to insert a text box for each parameter’s input value in the DataGrid (see FIGURE 4). The rest of the columns are also explicitly defined as BoundColumns to simplify the data binding process.

FIGURE 4: The DataGrid control explicitly specifies each of the columns that will be displayed in the table for the stored procedure’s parameters.

Using the same basic code as was used to retrieve the list of stored procedures from the database, the GetParametersButton_Click event retrieves the list of parameters associated with the selected stored procedure from the database using the sp_sproc_columns stored procedure (see FIGURE 5). The primary difference in this routine is that Parameters.Add is used to define the @procedure_name parameter to the SqlCommand object containing the stored procedure.

FIGURE 5: The GetParametersButton_Click event loads the information about the parameters of a stored procedure into the ParametersDataGrid.

Once the data adapter’s Fill method has populated a DataSet object, the resulting data table object is bound to the ParametersDataGrid control, which displays the parameters to the user. Finally, the ResultsDataGrid is turned off in preparation for executing the selected stored procedure.

The sp_sproc_columns stored procedure contains a lot of information besides the information displayed onscreen. For example, the NULLABLE column indicates if a column can contain null values, the COLUMN_DEF column contains the default value for the column, and the ORDINAL_POSITION contains the relative position of each parameter in the stored procedure.

Clicking the Execute Query button runs the code shown in FIGURE 6. After defining a connection object using the information from the web form, a SqlCommand object is instantiated using the SqlConnection object and the currently selected stored procedure from the drop-down list on the web form. A SqlDataAdapter object is also created, which will be used to retrieve any data that the stored procedure may return from the database.

FIGURE 6: Clicking the Execute Query button runs the currently selected stored procedure from the web form.

Using the newly created command object, the CommandType is set to StoredProcedure and then the AddParameters routine is called to create the SqlParameters collection for the SqlCommand object. The SqlDataAdapter executes the query and stores any returned data into a DataSet object. The UpdateParameters subroutine copies the values from the stored procedure’s parameters to the web form. Then DataTable object containing the returned data is bound to the ResultsDataGrid on the web form. If no records are returned, the DataGrid won’t be displayed. 

The real trick to making this program work is the AddParameters routine shown in FIGURE 7. This routine iterates through the collection of rows in the DataGrid. A Select Case statement examines the data type information contained in the second column of the DataGrid and chooses the appropriate statement to add the parameter to the SqlParameters collection.

FIGURE 7: Adding parameters to the SqlParameters collection involves determining the associated data type.

Note that the @RETURN_VALUE parameter is skipped. Return values have a COLUMN_TYPE of 5, which is stored in the sixth column of the DataGrid.

Only two types are shown in FIGURE 7 to keep the listing short, but these two types illustrate the basic approach to adding the parameter. The name of the parameter is always stored in the first column of the DataGrid. The CType function casts the value returned from the Controls collection to a TableCell, and then the actual value of the cell can be extracted from the Text property. The value of the parameter can be converted from the TextBox control to the Integer value associated by the parameter.

The Varchar data type illustrates how additional information about the data type, such as the length, is extracted from the information stored in the DataGrid.  Other data types would be handled the same way.

After the Select Case statement adds the parameter to the collection, the sixth column in the DataGrid is checked to see if the Direction property must be set to InputOutput for the parameter. A value of 2 means the parameter can return a value from the stored procedure, while a value of 1 means the parameter is an input parameter and a value of 5 indicates that the parameter contains the return value for the stored procedure.

The UpdateParameters routine contains a single For loop that scans through the rows in the DataGrid and updates the value in the last column of the DataGrid using the information from the parameter listed in the first column. Note that since the return value isn’t a real parameter, its value isn’t copied to the DataGrid.

After entering a valid CustomerId value and clicking the Execute Query button, the updated parameters DataGrid is shown in FIGURE 8. Had the stored procedure returned a row set, the ResultsDataGrid containing the row set would appear beneath the parameters DataGrid.

FIGURE 8: After executing the stored procedure, any returned values are displayed in the ParametersDataGrid.