Invoke A Stored Procedure from MultiStatement Table Valued Function - Step by Step

Introduction

A Multi-Statement Table-Valued user-defined function returns a table. We know that a Stored Procedure return a result set which can be hold in a table. In this article we will look into how we can achieve the same. We will perform the experiment in SQL Server 2014's tempdb

Straight to Experiment - Step by Step

Step 1: Create and Populate a table

Let us first create a table say [dbo].[tblWayPoints]

USE [tempdb]
GO

/****** Object:  Table [dbo].[tblWayPoints]    Script Date: 8/8/2016 8:51:56 AM ******/
DROP TABLE [dbo].[tblWayPoints]
GO

/****** Object:  Table [dbo].[tblWayPoints]    Script Date: 8/8/2016 8:51:56 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblWayPoints](
	[WayPointsID] [int] IDENTITY(1,1) NOT NULL,
	[Latitude] [varchar](500) NOT NULL,
	[Longitude] [varchar](500) NOT NULL,
	[PhoneNumber] [varchar](20) NOT NULL,
 CONSTRAINT [PK_tblWayPoints] PRIMARY KEY CLUSTERED 
(
	[WayPointsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Populate some values to the above table as under

USE [tempdb]
GO
SET IDENTITY_INSERT [dbo].[tblWayPoints] ON 

GO
INSERT [dbo].[tblWayPoints] ([WayPointsID], [Latitude], [Longitude], [PhoneNumber]) VALUES (1, N'-34.668516349643596', N'150.81978125', N'9886558702')
GO
INSERT [dbo].[tblWayPoints] ([WayPointsID], [Latitude], [Longitude], [PhoneNumber]) VALUES (2, N'-35.09660198927241', N'150.457232421875', N'9886558702')
GO
INSERT [dbo].[tblWayPoints] ([WayPointsID], [Latitude], [Longitude], [PhoneNumber]) VALUES (3, N'-34.713682925540354', N'149.74312109375', N'9886558702')
GO
INSERT [dbo].[tblWayPoints] ([WayPointsID], [Latitude], [Longitude], [PhoneNumber]) VALUES (4, N'-34.27452911659509', N'149.6167783203125', N'9886558702')
GO
INSERT [dbo].[tblWayPoints] ([WayPointsID], [Latitude], [Longitude], [PhoneNumber]) VALUES (5, N'-33.56343076390862', N'150.2869443359375', N'9886558702')
GO
SET IDENTITY_INSERT [dbo].[tblWayPoints] OFF
GO

Project the result

USE [tempdb]
GO
SELECT *
FROM [dbo].[tblWayPoints]
GO

Step 2: Create the Stored Procedure

USE [tempdb]
GO
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Niladri Biswas
-- Create date: 8/8/2016
-- Description:	Fetch records from [dbo].[tblWayPoints]
 -- EXEC [dbo].[usp_GetWayPoints]
-- =============================================
CREATE PROCEDURE [dbo].[usp_GetWayPoints] 	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT 
		[WayPointsID]
		,[Latitude]
		,[Longitude]
		,[PhoneNumber]
  FROM [ExperimentalDB].[dbo].[tblWayPoints]
END
GO

Step 3: Create the MultiStatement Table Valued Function

USE [tempdb]
GO
-- ================================================
-- Template generated from Template Explorer using:
-- Create Multi-Statement Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ============================================================
-- Author:		Niladri Biswas
-- Create date: 8/8/2016
-- Description:	Invoke the SP [tempdb].[dbo].[usp_GetWayPoints]
-- SELECT * FROM [tempdb].[dbo].[udfInvokeWayPointSP]()
-- ============================================================
CREATE FUNCTION [dbo].[udfInvokeWayPointSP]() 
Returns @ResultTbl Table 
(
	-- Add the parameters for the function here
		[WayPointsID]  INT NOT NULL,
		[Latitude]  Varchar(500) NOT NULL,
		[Longitude]  Varchar(500) NOT NULL,
		[PhoneNumber]  Varchar(20) NOT NULL
)

AS
BEGIN
	-- Fill the table variable with the rows for your result set
	
		INSERT @ResultTbl

        SELECT * 
		FROM OPENROWSET('SQLNCLI11', 'Server=YourServerName;UID=YourDBUserName;Pwd=YourDBPassword;','Exec [tempdb].[dbo].[usp_GetWayPoints] WITH RESULT SETS (( WayPointsID INT,Latitude Varchar(500),Longitude Varchar(500),PhoneNumber Varchar(20)));') AS X

        RETURN 
END
GO

OPENROWSET is a T-SQL function that allows for reading data from many sources. Since we are using SQL Server 2014, the provider will be SQLNCLI11.

From SQL Server 2012(Denali), we have With Result Sets feature. It allows us to change the column names and data types of the result set that a stored procedure returns. This feature has been used here since from SQL Server 2012 onwards, we need to use WITH RESULT SETS to explicitly describe the result set.

Step 4: Change global configuration settings for the current server

sp_configure changes global configuration settings for the current server. So we need to do the following

  
Sp_Configure 'show advanced options',1

Reconfigure with Override

Go

Sp_Configure 'Ad Hoc Distributed Queries',1

Reconfigure with Override

Go

Step 5: Execute the function

SELECT * FROM [tempdb].[dbo].[udfInvokeWayPointSP]()

Conclusion

Hope this article will help others to understand the usage of OPENROWSET and how to invoke a stored procedure from inside a function. Thanks for reading. Zipped file attached.