Select from Stored Procedure in SQL Function using OpenQuery
SQL functions are limited executable units when compared with SQL stored procedures. Select from stored procedure in SQL Server T-SQL programming is possible using OpenQuery. Using similar SQL programming tricks, SQL developers can execute SQL stored procedure in user defined sql function. Directly from SQL OpenQuery query, data rows can be filtered with a SELECT statement as illustrated in this SQL tutorial.
Below SQL stored procedure returns a list of stored procedures with execution data like sp name, database name, execution count, total elapsed time, and average elapsed time, etc. You can modify below SQL stored procedure or SQL Select statement by looking at returned columns of sys.dm_exec_procedure_stats SQL Server dynamic system view.
CREATE PROC sp_TopProceduresByAverageElapsedTime (@n smallint)
AS
SELECT TOP (@n)
DB_NAME(database_id) database_name,
OBJECT_NAME(object_id, database_id) procedure_name,
total_elapsed_time,
execution_count,
total_elapsed_time / execution_count AS avg_elapsed_time
FROM sys.dm_exec_procedure_stats
ORDER BY total_elapsed_time / execution_count DESC
It is possible to select from stored procedure using SQL OPENQUERY command as seen in below sql script. Besides using SQL Server OpenQuery, sql command is executed over the same instance using [.] loopback linked server which points itself.
SELECT * FROM OPENQUERY([.],
'Exec KodyazSQL.dbo.sp_TopProceduresByAverageElapsedTime 5')
as [OpenQuery]
If sql developers execute above Transact-SQL script, the output will be similar as follows.
Now we can create SQL Server table valued function which returns top n stored procedures from sys.dm_exec_procedure_stats system view using sp_TopProceduresByAverageElapsedTime procedure.
CREATE FUNCTION udf_TopProceduresByAverageElapsedTime ()
Returns @procedures table (
[database name] sysname,
[procedure name] sysname,
total_elapsed_time bigint,
execution_count bigint,
avg_elapsed_time bigint
)
AS
BEGIN
Insert Into @procedures
SELECT * FROM OPENQUERY([.], 'Exec KodyazSQL.dbo.sp_TopProceduresByAverageElapsedTime 5')
Return;
END
Within the SQL function, T-SQL programmers can execute sql select from stored procedure and filter output data using OpenQuery command. The returned output rows from SQL Server OpenQuery command is stored in the table valued function @procedures table. The @procedures table is the output of the user defined sql function.
Here is how an SQL database administrator or T-SQL developer can use udf_TopProceduresByAverageElapsedTime sql function to select from stored procedure within function code.
SELECT * FROM dbo.udf_TopProceduresByAverageElapsedTime()
Here is sample output for SQL Server function udf_TopProceduresByAverageElapsedTime
Although SQL functions have many usage limits, it is sometimes possible to overcome some of these limitation using SQL OpenQuery command as illustrated in this SQL tutorial.