Reusing T-SQL Code - Reusing Parameterized Queries: Stored Procedures versus Inline UDFs

4/30/2013 7:49:23 PM

If we want to reuse parameterized queries, it is usually preferable to wrap them in user-defined functions. It is typically less convenient to reuse parameterized queries that are wrapped in stored procedures, as the following examples will demonstrate.

Let's say we have a stored procedure that returns all sales for the month, across all states, as shown in Listing 1.

Listing 1. A stored procedure that returns all sales for the month.

Hopefully, you spotted the missed opportunity for code reuse in this listing. We should have reused our MonthReportingPeriodStart in the WHERE clause; I leave this as an exercise for the reader.

We now need to develop a stored procedure that retrieves the total sales per state for a given month, and we want to reuse the SelectSalesForMonth stored procedure, Although it's possible to do this, we will need to create a table variable or a temporary table with a structure that matches the structure of the result set returned by stored procedure, as shown in Listing 2.

Listing 2. The SelectSalesPerStateForMonth stored procedure, which reuses the SelectSalesForMonth stored procedure and returns total sales per state for the month.

We can run a smoke test to verify that our two stored procedures work.

Listing 3. Testing the new stored procedures.

So far so good; we have reused the code wrapped in SelectSalesForMonth procedure and it works. However, now suppose we want to select the state with the highest total sales for a given month. It looks as if we can simply reuse the SelectSalesPerStateForMonth procedure, again with a slight modification to create a table variable or a temporary table, as shown in Listing 4.

Listing 4. Reusing SelectSalesPerStateForMonth procedure to get the state with most sales.

Unfortunately, although the procedure creates, it does not work.

Listing 5. An INSERT...EXEC statement cannot be nested. Note that the exact error message may vary depending on the version of your SQL Server.

Unfortunately, the INSERT...EXEC approach that we used in SelectSalesPerStateForMonth procedure cannot be nested. This is a very serious limitation.

The two inline UDFs shown in Listing 5-18 implement the same requirements. Note that the TotalSalesPerStateForMonth function implements the same functionality as our previous SelectTotalSalesPerStateForMonth stored procedure.

As per our rules of code reuse, we would only ever implement one or the other, not both, in our solutions.

Listing 6. Implementing the same functionality via inline UDFs.

In contrast to what we saw in Listing 5, our attempt to reuse result sets returned from nested inline UDFs works just fine.

Listing 7. Testing the inline UDFs.

It is often easier to reuse code when it is wrapped in inline UDFs than when it is wrapped in stored procedures. I should emphasize that I refer only to inline UDFs, not to all three varieties of UDF. Whenever we are deciding whether to use stored procedures or UDFs, we also need to consider the following:

  • INSERT EXEC requires you to create a table variable or temporary table before doing the call; stored procedures can have multiple and/or varying result sets, depending on code path, causing all kinds of problems with INSERT EXEC

  • some functionality, such as data modifications and TRY...CATCH blocks, is not allowed in UDFs

  • the inline UDF, like a view, is expanded in the execution plan, giving the optimizer the choice to take shortcuts, or even remove joined tables if their columns are not used.

Let's discuss performance considerations and see why it might not be a good idea to use scalar UDFs.

Most View
Spring Is Here (Part 2)
Is 802.11ac Worth Adopting?
BlackBerry Z10 - A Touchscreen-Based Smartphone (Part 1)
LG Intuition Review - Skirts The Line Between Smartphone And Tablet (Part 5)
Fujifilm X-E1 - A Retro Camera That Inspires (Part 4)
My SQL : Replication for High Availability - Procedures (part 6) - Slave Promotion - A revised method for promoting a slave
10 Contenders For The 'Ultimate Protector' Crown (Part 3) : Eset Smart Security 6, Kaspersky Internet Security 2013, Zonealarm Internet Security 2013
HTC Desire C - Does It Have Anything Good?
Windows Phone 7 : Understanding Matrix Transformations (part 2) - Applying Multiple Transformations
How To Lock Windows By Image Password
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
Popular Tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
Top 10
OPEL MERIVA : Making a grand entrance
FORD MONDEO 2.0 ECOBOOST : Modern Mondeo
BMW 650i COUPE : Sexy retooling of BMW's 6-series
BMW 120d; M135i - Finely tuned
PHP Tutorials : Storing Images in MySQL with PHP (part 2) - Creating the HTML, Inserting the Image into MySQL
PHP Tutorials : Storing Images in MySQL with PHP (part 1) - Why store binary files in MySQL using PHP?
Java Tutorials : Nested For Loop (part 2) - Program to create a Two-Dimensional Array
Java Tutorials : Nested For Loop (part 1)
C# Tutorial: Reading and Writing XML Files (part 2) - Reading XML Files
C# Tutorial: Reading and Writing XML Files (part 1) - Writing XML Files