Saturday, July 17, 2010

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...