Microsoft SQL Server 2014 Unleashed

Rewriting Stored Procedures as Functions

Before the advent of user-defined functions, if you wanted to do custom processing within SQL code, your only real option was to create stored procedures to do things that at times might work much better as functions. For example, you can’t use the result set of a stored procedure in a WHERE clause or to return a value to be used as a column in a select list. Using a stored procedure to perform calculations on one or more columns in a result set often required using a cursor to step through each row in a result set and pass the column values fetched, one at a time, to the stored procedure as parameters. This procedure then typically returned the computed value via an output parameter, which had to be ...