SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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
Code

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]
Code

If sql developers execute above Transact-SQL script, the output will be similar as follows.

select from stored procedure using SQL OPENQUERY command

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
Code

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()
Code

Here is sample output for SQL Server function udf_TopProceduresByAverageElapsedTime

select from stored procedure in sql function

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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.