Choosing a Stored Procedure Type
When working with SQL Server 2005, you have a choice between managed stored procedures and the standard type that relies on Transact-Structured Query Language (T-SQL). You can create and edit either form in Visual Studio. The choice you make normally depends on which language you want to use and your goals for the stored procedure.
Standard stored procedures (those created using T-SQL) offer several advantages over the managed type:
- Most important to the developer, standard stored procedures provide the best level of automation with Visual Studio. If you want to perform the least amount of work possible to obtain a usable result, choose a standard stored procedure.
- The database administrator (DBA) for your organization will understand standard stored procedures best, making it easier to obtain help and support as you create the stored procedure.
- Standard stored procedures won’t require as much code to perform the same task as a managed stored procedure would, because you only define the result you want, rather than how to perform the task.
Managed stored procedures also have a number of advantages:
- Because you have more control over how a managed stored procedure performs its work, you can better optimize the data manipulation for your particular needs.
- Since C# and VB provide significantly more advanced language features than those that T-SQL provides, you gain incredible flexibility when working with a managed stored procedure.
- Managed stored procedures can hide processing details in an assembly, making them essentially look like a black box to anyone using them.