Execute Stored Procedure from a Function

I know this has been asked to death, and I know why SQL Server doesn't let you do it.

But is there any workaround for this, other than using Extended Stored Procedures?

And please don't tell me to convert my function into a procedure...

So what I'm really asking is: Is there ANY way to run a Stored Procedure from within a Function?

EDIT:

Point proven: 开发者_JS百科there is a way around it, but it's so WRONG I wouldn't do it. I'm gonna change it to a Stored Procedure and execute it elsewhere.

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

Here is another possible workaround:

if exists (select * from master..sysservers where srvname = 'loopback')
    exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback', @srvproduct = N'', @provider = N'SQLOLEDB', @datasrc = @@servername
go

create function testit()
    returns int
as
begin
    declare @res int;
    select @res=count(*) from openquery(loopback, 'exec sp_who');
    return @res
end
go

select dbo.testit()

It's not so scary as xp_cmdshell but also has too many implications for practical use.

I have figured out a solution to this problem. We can build a Function or View with "rendered" sql in a stored procedure that can then be executed as normal.

1.Create another sproc

CREATE PROCEDURE [dbo].[usp_FunctionBuilder]
DECLARE @outerSql VARCHAR(MAX)
DECLARE @innerSql VARCHAR(MAX)

2.Build the dynamic sql that you want to execute in your function (Example: you could use a loop and union, you could read in another sproc, use if statements and parameters for conditional sql, etc.)

SET @innerSql = 'your sql'

3.Wrap the @innerSql in a create function statement and define any external parameters that you have used in the @innerSql so they can be passed into the generated function.

SET @outerSql = 'CREATE FUNCTION [dbo].[fn_GeneratedFunction] ( @Param varchar(10))
RETURNS TABLE
AS
RETURN
' + @innerSql;


EXEC(@outerSql)

This is just pseudocode but the solution solves many problems such as linked server limitations, parameters, dynamic sql in function, dynamic server/database/table name, loops, etc.

You will need to tweak it to your needs, (Example: changing the return in the function)