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.

SP_who filtering UDF vs SP,
Data Manipulation Language,
(Some) Differences (,
(Some) Differences (stackoverflow),

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: Logo

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