How to call stored procs from User Defined Functions (udf) in SQL Server

If you want to use stored procs from your functions , it is not possible with simple "exec sp_myproc".

You can use openquery to call your server as linked server and execute stored procs.
You need to run some commands before running openquery.


  Step-1: enable openquery
---open query to have access to stored procs within functions
--add your server as linked server
sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = @@servername

 Step-2: enable data access

EXEC sp_serveroption 'OMER-PC', 'DATA ACCESS', TRUE

     ---Check access

Step-3: Select * from openquery(LOCALSERVER, 'Exec sp_who')

This query should return some results. Openquery does not work for every stored proc. You need to look into the details.

Some simple examples:


1)--inline table function calling stored proc
-----------------------------------------------------------
create FUNCTION Test.udfCallProcInline()
 returns  table
AS
return
      select * from openquery(LOCALSERVER, 'Exec sys.sp_tables')
 GO

--------------------------------------------------------------

You can call your function as normal udfs:

select * from Test.udfCallProcInline()

It is also possible to make joins and select columns from the results. You don't need to know column data types here.

2-)-multi statement function

--you may need to check column data types here

CREATE FUNCTION Test.udfCallAProc2() 

 returns @mytable table

     (table_qualifier varchar(50),

table_owner varchar(50),

table_name varchar(50),

table_type varchar(50),

remarks  varchar(255)

)

AS

begin 

        insert @mytable 

      select * from openquery(LOCALSERVER, 'Exec sys.sp_tables')

 return 

 end

GO

select * from Test.udfCallAProc2()

----More examples....later...