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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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.

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

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

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.







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems