More UDFs and Stored Procedures

Today I'm working with functions again and examining the differences between User Defined Functions (UDF) and stored procedures (SP).

• Both take parameters, but stored procedures also accept OUTPUT parameters. These are used to get modified values from stored procedures.
• Return values are optional in stored procedures.
• Stored procedures can be used to create other objects.
• Stored procedures can update data.
• Stored procedures can call a proc.

I've been told by other developers that anything client facing should use stored procedures. Why is that? The reasoning is pretty simple: Processes outside of SQL Server should not have direct access to tables, views, or functions. This helps to prevent things like SQL injection attacks.

But there are other benefits as well. They allow for more modular development by separating the data application layer from other programming logic. A stored procedure can be created once at the database layer and then other developers can concentrate on their code instead of SQL. The database logic can then be modified independently of the program's source code.

Stored procedures can also be used to reduce network traffic. They are generally faster because they are compiled when first run and the query plans are cached by SQL Server's optimizer. You can perform an operation that requires many lines of SQL code through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.

Here's a simple refresher on constructing stored procedures in T-SQL:

As usual, I'm working with the MS produced "AdventureWorks" database in SQL Server 2008 R2. This script creates a stored procedure with a default value for @CustomerID. There are two calls to the procedure. When a parameter value is specified, the SP returns the single corresponding record. When no value is passed in, all the records are returned.