|
||||||
 |
||||||
|
Tech•Ed, May 26, 2004
Note: William Vaughn is presenting "Managing Stored Procedures for Performance" at Tech•Ed on Friday, May 28. This tip is from that session. In my Tech•Ed 2004 session, I'll examine how SQL Server manages stored procedures and builds a query plan based on the entire procedure—whether or not the logic is completely executed. You will find that it's a great idea to break up large, complex stored procedures into smaller blocks of T-SQL code. One of the issues developers face with today's version of Visual Studio .NET is how to call complex stored procedures that have numerous input parameters. ADO.NET always uses named parameters when accessing SQL Server 2000, so it's possible to construct "sparse" Parameter lists. If you add a Parameter object, you must provide a value for the Parameter. If the Parameter is not added to the Parameters collection, the stored procedure uses the default value. Microsoft suggests that you embed logic that decides how to construct the Parameters collection each time the stored procedure is called. This means that each section of your code that calls the stored procedure must add conditions around the construction phase of the Parameters collection. This logic either adds a needed Parameter or skips over it. I'm not thrilled with this approach, because it distributes the logic needed to call the stored procedure all over the application. I would like to see a new property added to the Parameter object—one that would indicate whether it will be used when the stored procedure executes. I don't have much hope this approach will be implemented, though. About the Author |