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
SELECT * FROM OPENQUERY([.],
'Exec KodyazSQL.dbo.sp_TopProceduresByAverageElapsedTime 5')
as [OpenQuery]
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
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()
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.
EmoticonEmoticon