| OBJECTIVES Learn to execute stored procedures that return data Learn to pass parameters to a stored procedure Learn to use OUTPUT para-meters It's likely that any reasonably sized database application will need to execute stored procedures at some point. You might use stored procedures to return data or to execute action queries. There are many reasons why you might want to use stored procedures as part of an ASP.NET application. We can't list them all, and you may have your own reasons. However, here are some of the more common reasons: You have no choice you need to retrieve or manipulate data, and you don't control the database server. Your DBA told you that you must use stored procedures. Stored procedures are generally more efficient than using dynamic SQL strings as part of your code. Because the database server can precompile and preoptimize the query's execution plan, your applications run more efficiently. Stored procedures put the database-handling code where it ought to be on the database server. There are many reasons to include data-manipulation code in your application, but duplicating the same queries throughout your code is a maintenance nightmare. Placing data-manipulation and retrieval code all in one place makes it much easier to maintain your application. Stored procedures allow you to pass parameters that can control the runtime activity. Although you can mimic this behavior in code, by simply building a different SQL string for each way you might use the stored procedure, you lose the precompiled execution plan when you use that technique. Stored procedures can accept parameters as input parameters, output parameters, or both. We feel quite strongly that you'll do better by placing most, if not all, your data-manipulation and retrieval code in stored procedures, if at all possible. Although we won't spend time in this book discussing the details of creating stored procedures, it's a topic you should at least be conversant in. In addition, because we don't want to meddle with your installation of SQL Server too very much, we'll only use stored procedures in this single chapter. TIP Of course, stored procedures are only available on database server applications, such as SQL Server, Oracle, Sybase, and so on. If you're working with data stored in an Access/Jet database, you won't be using stored procedures. However, you'll still need to be able to pass parameters into the query (Access queries don't allow output parameters), and you'll need some way to execute the query and retrieve its results. Certainly, running an Access query is far more limited than using a stored procedure, but many of the same concepts apply.
In this chapter, you'll use stored procedures to manipulate data in SQL Server tables. You'll add stored procedures to the Northwind database in SQL Server and use these procedures to add, edit, and delete rows from the tables in the sample database. |
