ADO.NET database developers frequently use T-SQL strings to retrieve data for their applications. When ADO.NET developers have a poor understanding of a database's design, the T-SQL language, or both, the process of creating T-SQL strings for their applications can lead to inefficient data-access designs or even result sets with improperly computed values. In such situations, reusing T-SQL code in database objects such as stored procedures and user-defined functions (UDFs) offers distinct advantages. Reusing code in database objects can help simplify data-access tasks and secure a database's schema. A senior developer or DBA can prepare a T-SQL code library that intermediate- and junior-level developers can reuse for many common data-access tasks. And the developer or DBA can require client applications to connect through security accounts that have permission to invoke the library's stored procedures and UDFs—but not necessarily the underlying objects.
Let's learn the techniques for processing stored procedures and UDFs with ADO.NET and Visual Basic .NET. We'll walk through two sample applications the Categories and Products tables from the sample Northwind database. You can join these two tables by their CategoryID column values. You can adapt this article's code samples to apply to any pair of tables that join by one or more pairs of column values. The T-SQL code samples are also helpful in another sense: They demonstrate the techniques you can use to improve the reusability of T-SQL code. These techniques include the use of input parameters, output parameters, and return status values with stored procedures as well as the use of scalar and table-valued returned values from UDFs. By learning and applying these techniques in your applications, you can dramatically increase the reusability of the T-SQL code in your stored procedures and UDFs. (You can, though, apply the Visual Basic .NET techniques we use in this article to any .NET language that implements ADO.NET programming support, such as C#.)
Loading the Library
The T-SQL code library for this article's example consists of a stored procedure and three UDFs, all of which reside in the SSMProcFunc database. At InstantDoc ID 45059, you can download the SqlMagProcFuncSqlScript.zip file, which contains the complete T-SQL code for creating the SSMProcFunc database and its objects. The code also creates a login that has access to the SSMProcFunc database and grants permission for the login's user account to invoke database objects containing T-SQL code.
Let's take a closer look at the code you'll use to create the database objects that the application will use. The stored procedure and three UDFs represent complementary techniques for achieving the same result, so you'll typically use one or the other, but not both. As you apply the techniques, you'll discover cases in which one approach or the other is more optimal for a particular application context.
The stored procedure. lt set, an output parameter, and a return status value. The stored procedure accepts an input parameter, @categoryID, and works well for calling applications that need one or more scalar values, such as aggregate values, along with a result set.
Listing 1 includes two comma-delimited parameter declarations. The @CategoryID input parameter can accept an int value from a calling application, such as an ADO.NET application. The upDiscontinuedProducts stored procedure returns the current number of discontinued products through the @DiscontinuedCount output parameter.
The SELECT statement returns three columns (CategoryID, CategoryName, and ProductName) from the Northwind Categories and Products tables. These columns belong to a result set that an ADO.NET application can access by invoking the stored procedure. The WHERE clause specifies three criteria that perform an inner join between the Categories and Products tables.
The SET and RETURN statements conclude the upDiscontinuedProducts stored procedure. The SET statement assigns the @@ROWCOUNT value to the @DiscontinuedCount output parameter. The variable @@ROWCOUNT indicates the number of rows the last statement affected, which in this stored procedure is the number of discontinued products in a category. The RETURN statement assigns to the stored procedure's return status the total number of rows in the Products table in a category.
Three UDFs. A UDF can return either a scalar value or a table value. A scalar return value corresponds generally to a stored procedure's output parameter. But a UDF requires the declaration of its data type in its RETURNS clause. The UDF uses a RETURN clause to pass back a value to its calling application. You can optionally pass one or more parameters to a UDF.
Because one UDF can return just one value, you have to use three UDFs to perform the role of the upDiscontinuedProducts stored procedure. In the context of this application, the stored procedure is more robust, but its code is more complex than the code of any of the three UDFs. In addition, you can reference the return from a table-valued UDF in a FROM clause. T-SQL doesn't support referencing a stored procedure's result set in a FROM clause.
Listing 2 shows the first UDF, ufProductsInCategory, which returns the total number of rows from the Products table matching the @CategoryID parameter value that a calling application passes to the UDF. Listing 2's code starts by removing any previous version of the UDF. Because both stored procedures and UDFs can apply identical code for removing a previously existing object, a good practice is to use distinct names for stored procedures and UDFs.
The code for the second UDF, ufDiscontinuedProductsInCategory, is nearly identical to the code in Listing 2, but this UDF returns a count for just the discontinued products in a category. To create the ufDiscontinuedProductsInCategory UDF, simply change the name references in the DROP and CREATE statements and replace the SELECT statement in Listing 2 with the following code:
SELECT COUNT(*)
FROM Northwind.dbo.Products AS np
WHERE np.CategoryID = @CategoryID
AND Discontinued = 1
Listing 3 shows the third UDF, ufDiscontinuedProductRowsInCategory, which is a table-valued UDF. This type of UDF returns a set of rows that you can reference through the FROM clause of a SELECT statement. Notice that the UDF's RETURNS clause specifies a table data type. The RETURN clause includes a SELECT statement that populates the table variable. This SELECT statement is the same one in the upDiscontinuedProducts stored procedure.
Prev. page
[1]
2
next page 
