Support both stored procedures and functions with same TSQL code base? 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.
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: