Execute Stored Procedure from a Function
EDIT: I haven't tried this, so I can't vouch for it! And you already know you shouldn't be doing this, so please don't do it. BUT...
Try looking here: http://sqlblog.com/blogs/denis_gobo/archive/2008/05/08/6703.aspx
The key bit is this bit which I have attempted to tweak for your purposes:
DECLARE @SQL varchar(500)SELECT @SQL = 'osql -S' +@@servername +' -E -q "exec dbName..sprocName "'EXEC master..xp_cmdshell @SQL
Functions are not allowed to have side-effects such as altering table contents.
Stored Procedures are.
If a function called a stored procedure, the function would become able to have side-effects.
So, sorry, but no, you can't call a stored procedure from a function.
Another option, in addition to using OPENQUERY and xp_cmdshell, is to use SQLCLR (SQL Server's "CLR Integration" feature). Not only is the SQLCLR option more secure than those other two methods, but there is also the potential benefit of being able to call the stored procedure in the current session such that it would have access to any session-based objects or settings, such as:
- temporary tables
- temporary stored procedures
- CONTEXT_INFO
This can be achieved by using "context connection = true;" as the ConnectionString. Just keep in mind that all other restrictions placed on T-SQL User-Defined Functions will be enforced (i.e. cannot have any side-effects).
If you use a regular connection (i.e. not using the context connection), then it will operate as an independent call, just like it does when using the OPENQUERY and xp_cmdshell methods.
HOWEVER, please keep in mind that if you will be using a function that calls a stored procedure (regardless of which of the 3 noted methods you use) in a statement that affects more than 1 row, then the behavior cannot be expected to run once per row. As @MartinSmith mentioned in a comment on @MatBailie's answer, the Query Optimizer does not guarantee either the timing or number of executions of functions. But if you are using it in a SET @Variable = function(); statement or SELECT * FROM function(); query, then it should be ok.
An example of using a .NET / C# SQLCLR user-defined function to execute a stored procedure is shown in the following article (which I wrote):
Stairway to SQLCLR Level 2: Sample Stored Procedure and Function