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.