Calling SQL 2005 function from C#.NET 2.0

Using Enterprise Library, C# .NET 2, SQL 2005

I have some code that I need to run in SQL 2005 that really makes sense to be a function rather than a stored procedure (2 such instances actually).  They take in several variables and return a single value as a result (not that it matters, but in case you are curious one returns a bit, the other returns a dateTime).

I currently have them written as scalar functions.

However, I need to call them from both a) within other stored procedures and b) directly from .NET

I have come up with the following options:

Option 1
-----------
Wrap the function in a stored procedure
Call stored proc from .NET

Option 2
-----------
Convert the functions into a stored procedure - a little more work when calling from within SQL, normal call of stored proc from .NET

Option 3
-----------
Call function using SQL statement in .NET

Option 4
----------
Something else that lets me call the function as a function from .NET - DOn't know if this is even possible

None of the options 1-3 are particularly great, but 4 I don't know if it is possible.  We don't like option 3, because we would prefer to use stored procedures for controlling permissions, etc.  We don't like option 1 because it seems a waste to have a stored proc that is just a warpper call to the function.  We don't like option 2 because of the extra work when calling the functions from within other stored procs.

Any ideas?  It seems like this should be possible in a non-hack way (which 1-3 all feel like).

ThanksStart Free Trial