Support both stored procedures and functions with same TSQL code base?

Question

We have a need for both Microsoft SQL Server 2008 functions (ITVFs) and stored procedures to provide the same interface and functionality to end-user reporting frontends.

Given the following constraints:

• Functions may not call stored procedures.
• Stored procedures may call functions.

does putting all the TSQL source code into functions and using stored procedures as wrappers around calls to the functions makes sense?

What other options (if any) exist to support both functions and stored procedures with the same TSQL codebase?

---

For the curious....

• Frontend #1: Microsoft Access 2010 SP1 (version 14.0.6024.1000)

  • Users use Linked Tables > Views > ITVF's
  • This approach avoids necessity for developing frontend report screens/code
  • IT staff plan to sunset this environment in 2014-15 or so
  • Linked tables may access views but not functions or stored procedures
  • Views, in turn, may access functions but not stored procedures
  • Would still like to keep functions around for ad hoc queries even after sunsetting this frontend...though this is a would-be-nice not a need

• Frontend #2: ASP.NET 4.0 web app

  • To-be-developed by in-house IT staff
  • IT staff policy requires .NET web apps to call only stored procedures
• Backend: Microsoft SQL Server 2008 SP3 (version 10.0.5500...will likely upgrade to Microsoft SQL Server 2012 sometime in 2013)

UPDATE: to clarify, this represents a reporting app, so really only need read-only access, other than the ability to create incidental temp tables, table variables, and so forth.

Answer 1

Sounds like you've already made the decision to use T-SQL for your back-end. For OWASP reasons, you are correct in that calling only stored procedures from your data access layer is the safest approach to avoid SQL injection.

Stored procedures and functions are similar in that you can pass arguments for the parameter list. They also can do similar things. Code re-use is a good thing and will save you in the end when fixing bugs or making enhancements in the future. So find the logic that is similar and re-factor that code to functions. If you want to return tables (single result set), use table based functions. If you want to return single values, use scalar functions. Note, you won't be able to perform permanent environment changes with functions (no CRUD operations are allowed... CREATE/INSERT, READ, UPDATE, DELETE). It's also good practice to wrap your stored procedures with error/exception handling and return those exceptions to the server, to let your logging handle them appropriately. Also note that it's common to return multiple result sets in a single call to a stored procedure (3 SELECT statements for example). In your application code, you'd handle each table individually in the response.

Here are some differences:

Answer 2

From my point of view, you need to be careful that you really are talking about only ITVFs. Inline table-valued functions are basically parameterized views, so they are better optimizable compared to multi-statement table-valued functions.

However, this limits some of their functionality. I find it unlikely that all your logic for frontend #1 can be managed with ITVFs, but maybe. It's just as likely that they will duplicate logic in forms in Access unless all they are using is the linked tables and not trying to do any forms at all.

It's an OK strategy, but I still expect you might have some duplication of code.

As a strategy, it is probably sound because ITVFs are intrinsically pretty low on the complexity list - I tend to use the constructs in SQL in increasing order of complexity - trying to solve problems using the least complex structure possible:

Tables
Constraints
Computed Columns
Views
ITVFs
Stored Procs
Triggers
TVFs
Scalar Functions

If your ITVFs are very complex and expensive, you run into the danger of people building on them and incurring costs they don't need to incur because it's the only way to get some data - i.e. something which might be less expensive as a persisted computed column or something which is being computed in the function but most users don't really need - expensive things which few people need should obviously be in the last layer or a separate function.

Answer 3

As a point of clarification, you can bind forms/reports to stored procedures in Access. You do this by creating a pass-through query. The drawbacks are that this query will be read-only, and there's no simple way of passing parameter values to the procedure. You should be able to hard-code parameter values in the query text, though, and maybe even use VBA to rewrite the query text on the fly (never tried it, but you can do a lot of crazy things like that in Access).

However, if you're just using this for reporting, these might not be problems.

1. Create a new query in the query designer.
2. In the Query Type section of the ribbon (looking at Access 2010 at the moment), click the "Pass-Through" button.
3. Open the property sheet for the query, then in the "ODBC Connect Str" field, click the little ellipsis button.
4. You'll have to choose an ODBC connection, much like setting up a linked table.
5. Enter the query exactly as you want it passed to SQL Server.

After that, you'll have a query you can bind to a form or report, much like you would with a linked table or any other query. It will, of course, be read-only. You can still use all of Access' filtering and sorting functionality on the recordset, although it may not be quite as optimal, since Access has to fetch all the records and filter client side. Linked tables allow for some of this to happen server-side, I believe.

But going back to the original question, I think it's reasonable to encapsulate query code in user-defined functions. I do this in cases where I need to be certain that code won't be duplicated, and slowly drift out of sync in multiple places. For example, I have a function that selects an appropriate commission calculation rule based on assorted input parameters (salesperson, item properties, customer properties, etc), and this function is the only place where that happens. Any procedure that needs to calculate commissions cross/outer-applies to that function where needed. It's a good practice for cases where you need to be certain that the logic you implement stays in one location.