MSSQL User Defined Functions vs Stored Procedures

I received this question earlier today, and thought it was a valid question often misunderstood, and deserving of a small write-up:
“Should a User Defined Function be your first choice instead of a Stored Procedure?”

While there are many pros and cons of each not covered in this write-up (review your versions on MSDN for details), including some features which may not be apparent until you have an issue to troubleshoot (such as sp_who filtering), you can generally ask yourself a single question up front that can help you determine which you should use.

Simply, if the db functionality you need to implement in the function/procedure requires
any DML (insert/update/delete), then go with a stored procedure. Advanced selects and/or filters are best left up to views/table valued functions.

Additionally, do not be afraid to use a combination of functions and procedures especially if there is a goal of re-usability, in accordance with the design considering the planned growth of your db as your software & db architecture permits. On that note, consider and test the performance differences of these implementations, as a compiled/cached function/procedure containing more logic internally may outperform one utilizing logic that is spread throughout.

References
SP_who filtering UDF vs SP, http://stackoverflow.com/questions/2567141/use-sql-to-filter-the-results-of-a-stored-procedure
Data Manipulation Language, http://en.wikipedia.org/wiki/Data_manipulation_language
(Some) Differences (about.com), http://databases.about.com/od/sqlserver/a/procs_vs_functs.htm
(Some) Differences (stackoverflow), http://stackoverflow.com/questions/2039936/difference-between-stored-procedures-and-user-defined-functions

Advertisements

About Ronnie Diaz

Ronnie Diaz is an enterprise software engineer responsible for front-end and back-end development for companies in many industries. Heavily involved in cloud development, online retail, e-commerce and electronic ordering, fulfillment and customer relational systems.

Posted on October 14, 2013, in Language References, Programming & Development and tagged , , , , , , , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: