Executing Stored Procedures

I won't bore you with another lecture on the importance of stored proceduresyou've heard it a dozen times or more in this and earlier chapters, and from other folks in the industry that we both respect. Suffice it to say that if you're using SQL Server, I expect you to use stored procedures at one point in time or another. When setting up a SqlCommand to execute a stored procedure, you simply set the CommandType to "Stored Procedure" and build a Parameters collection to match the stored proceduredefined parameters. The names you use must match the names used in the stored procedure, and you must define and provide a Value setting for each stored procedure parameter that does not have a default value assigned in the stored procedure. That much should be clear by now. What I haven't talked about is the Direction property of the SqlParameter object.

Handling OUTPUT, INPUT/OUTPUT, and RETURN Value Parameters

All stored procedures returna RETURN value. While the stored procedure code might set the RETURN value, a value of 0 is sent nonetheless if no specific RETURN value is set. In order to trap this value, you'll need to add a SqlParameter to the SqlCommand Parameters collection and set the Direction property to ParameterDirection.ReturnValue. The name of this SqlParameter is not important, but the SqlDbType isit must be set to SqlDbType.Int. The RETURN value can be used for virtually anything that can be expressed as an Integer, but it usually indicates the success or failure of the stored procedure.

If the stored procedure returns OUTPUT parameters, you must create a SqlParameter object to capture the valueone for each OUTPUT parameter. No, these are not optionalif a stored procedure defines a SqlParameter as OUTPUT, you must define a SqlParameter with the Direction property set to ParameterDirection.Output. OUTPUT parameters can return any type of data except image, text, and ntext (except when working with CLR stored procedures).

If you want to use the same SqlParameter to pass a parameter value to and from a stored procedure OUTPUT parameter, you simply set the Direction property to ParameterDirection.InputOutput. In this case, you must provide a value for the SqlParameter before execution. Figure 10.33 shows a stored procedure used to demonstrate use of the various Parameter types. Note that this stored procedure returns OUTPUT and RETURN value parameters but no rowset.

Figure 10.33. The TestInOutNoRowset stored procedure.

The code shown in Figure 10.34 illustrates how to set up a SqlCommand to execute a stored procedure, set the input and input/OUTPUT parameters, and extract the OUTPUT and RETURN value parameters. I use the ExecuteNonQuery to execute the stored procedure. Because the stored procedure does not return a rowset, the RETURN value and OUTPUT parameters are available immediately. Remember, OUTPUT and RETURN parameter values are not returned until the last rowset is returned from the query.

Figure 10.34. Configuring a SqlCommand to execute the TestInOutNoRowset stored procedure.

If the stored procedure returns one or more rowsets, you'll have to capture or cancel all of the resultsets before any OUTPUT or RETURN value parameters are exposed. This means the SqlParameter Value property for the OUTPUT and RETURN value parameters won't contain meaningful information until all rowsets have been returned.

Handling Rowsets with OUTPUT Parameters

Before I move on, let's take a closer look at a fairly common scenariowhen a stored procedure returns two or more resultsets along with the OUTPUT parameters. The example in Figure 10.36 returns two resultsetseach with a rowsetand then returns two OUTPUT parameters. Before you peruse the code, take a look at the stored procedure (shown in Figure 10.35). Note how the each OUTPUT parameter captures the @@Rowcount value after the SELECT statement is executed. By this, one might assume that the first OUTPUT parameter would be made available directly after the first rowset is processedit's not. All OUTPUT parameters are returned by SQL Server after all resultsets and their rowsets (if any) are returned.

Figure 10.35. Return two resultsets containing rowsets with OUTPUT parameters.

IMHO

OUTPUT parameters are one of the most under-used and most misunderstood TSQL mechanisms.

So the code to execute this stored procedure uses the DataSet Load method to retrieve all resultsets and move any rowsets into the named DataTablesin this case, Load creates two new DataTables. The Load method is also doing something else for usit's closing the SqlDataReader when it completes rowset population. Once the SqlDataReader is closed, you'll be able to see the OUTPUT and RETURN value parameters. They won't be visible until theneven if the Read method (that I discuss in the next chapter) returns False (there are no more rows).

Figure 10.36. Executing a stored procedure that returns two resultsets and OUTPUT parameters.