[sql] 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: 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.

The answer is


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


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.


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)


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to sql-server-2005

Add a row number to result set of a SQL query SQL Server : Transpose rows to columns Select info from table where row has max date How to query for Xml values and attributes from table in SQL Server? How to restore SQL Server 2014 backup in SQL Server 2008 SQL Server 2005 Using CHARINDEX() To split a string Is it necessary to use # for creating temp tables in SQL server? SQL Query to find the last day of the month JDBC connection to MSSQL server in windows authentication mode How to convert the system date format to dd/mm/yy in SQL Server 2008 R2?

Examples related to stored-procedures

How to create temp table using Create statement in SQL Server? How do I pass a list as a parameter in a stored procedure? SQL Server IF EXISTS THEN 1 ELSE 2 Stored procedure with default parameters Could not find server 'server name' in sys.servers. SQL Server 2014 How to kill all active and inactive oracle sessions for user EXEC sp_executesql with multiple parameters MySQL stored procedure return value SQL Server: use CASE with LIKE SQL server stored procedure return a table

Examples related to user-defined-functions

Hive: Convert String to Integer Declare variable in table valued function Execute Stored Procedure from a Function How to call a MySQL stored procedure from within PHP code? Multi-statement Table Valued Function vs Inline Table Valued Function Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Splitfn", or the name is ambiguous Pass table as parameter into sql server UDF How to strip all non-alphabetic characters from string in SQL Server? How to determine the number of days in a month in SQL Server? TSQL How do you output PRINT in a user defined function?