The Differences between functions and stored procedures



Functions (select data only)
SPs (can modify data)
Purpose
For returning a computation value used in other T-SQL statements
For performing business logic with a set of complex T-SQL statements, or interacting with outside world.
Which T-SQL elements can be used inside?
SELECT only (cannot modify the data or objects)
DML, DDL, Transaction, Error Handling
How to be used?
SELECT, WHERE, HAVING
EXEC (for deterministic functions)
EXEC
Deferred Name Resolution?
No
Yes
Need input parameters?
Optional (0 ~ 1023)
Optional (0 ~ 21000)
Which SQL Server data types can be used as input parameters?
All including table variable but not text, ntext, image, timestamps
Any SQL Server data types including table variable (companying with READONLY).
Have to return a data?
Yes (a single value or a table)
Optional (0 ~ Many) – using OUTPUT or OUT
Which SQL Server data types can be used as output parameters?
All including table variable but not text, ntext, image, timestamps
Any SQL Server data types, but not a table variable. Cursor is used as output only accompanied by the VARYING keyword.
Return a status code?
No
Yes
Can a temp table be used inside?
No (but table variable is OK)
Yes (table variable OK too)
Can call another function
Yes
Yes
Can call another SP?
No (but can call an extended stored procedure.)
Yes
Can be used for a dynamic SQL?
No
Yes
Can a non-deterministic function be used?
No
Yes
Typical use cases

  • SELECT – inline function
  • WHERE
  • HAVING
SQL Codes in the applications vs. Stored Procedure in SQL Server?

The benefits of using stored procedures in SQL Server rather than application code stored locally on client computers include:

  • They allow modular programming.
  • They allow faster execution.
  •  They can reduce network traffic.
  • They can be used as a security mechanism.
  •  Protect against SQL injection attacks.
When to use SPs? – if the codes need to be reused

  • If the operation requires a large amount of T-SQL code or is performed repetitively,
  • If an operation requires hundreds of lines of T-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.
  • If you want to grant users permission to execute a stored procedure but not permission to execute the procedure's statements directly.
  • In dynamic SQL,if you want to pass data values to a backend stored procedure.
When to place SQL directly in your application? – If the codes are dynamic

  • When the SQL is generated on the fly. That is, you cannot create and cache an execution plan since it's different each time it's called.  Or the code is executed will depend on your environment (i.e., the server). Its dynamic nature negates many of the advantages of a stored procedure.
  •  In dynamic SQL，if you want to pass data values to the application code to construct the T-SQL string.
Caveat

  • Use other options instead of UDF if it makes the query non-sargable or cursor-type.
  • Create the function with SchemaBinding

  • Include SET NOCOUNT ON statement
  •  Use schema name with object name
  • Generally do not use the prefix “sp_” in the stored procedure name
  • Use IF EXISTS (SELECT 1) instead of (SELECT *)
  • Use the sp_executesql stored procedure instead of the EXECUTE (@AString)
  • Try to avoid using SQL Server cursors whenever possible
  •  Keep the Transaction as short as possible
  • Use TRY-Catch for error handling