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

About Ronnie Diaz

Ronnie Diaz is a software engineer and tech consultant. Ronnie started his career in front-end and back-end development for companies in ecommerce, service industries and remote education. This work transitioned from traditional desktop client-server applications through early cloud development. Software included human resource management and service technician workflows, online retail e-commerce and electronic ordering and fulfillment, IVR customer relational systems, and video streaming remote learning SCORM web applications. Hands on server experience and software performance optimization led to creation of a startup business focused on collocated data center services and continued experience with video streaming hardware and software. This led to a career in Amazon Prime Video where Ronnie is currently employed, building software and systems which stream live sports and events for millions of viewers around the world.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: