Execute Stored Procedure from a Function

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Execute Stored Procedure from a Function

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to stored-procedures

Examples related to user-defined-functions