Calling a Procedure from a Function in SQL Server

Hello to everyone,

In this article, I will try to give you information about calling a procedure from within a function in SQL Server.

Many of you started reading the article saying that such a thing would not happen, or you started reading the article wondering how it really is.

A common concept is: A procedure cannot be called within a function, it cannot be called.

Of course, we will do the process in a different way here.

OLE DB provider connection in SQL Server can run a procedure inside the function using MSDASQL with the help of OPENROWSET().

Users can perform this operation by using database and server information together with MSDASQL within the OPENROWSET() connection.

You can easily do this using the code below. I kept the process simple as an example. You can use it in a more complicated way.

--Creating a table

CREATE TABLE NumberTable
(
    ID INT PRIMARY KEY
);

--Adding data to the table

INSERT INTO NumberTable
VALUES
(1  ),
(2),
(3),
(4),
(5),
(6),
(7),
(8);


--Procedure creation

CREATE PROCEDURE NumberTableProcedure
AS
BEGIN
    SELECT *
    FROM NumberTable;
END;

--Making the Settings

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

--Fonksiyon oluşturulması

CREATE FUNCTION fn_NumberFunction
()
RETURNS TABLE
AS
RETURN SELECT *
       FROM
           OPENROWSET('MSDASQL',
                      'DRIVER={SQL Server}; SERVER=YAVUZSELIM\YAVUZSELIM; Trusted_Connection=yes',
                      'EXEC TESTDB..NumberTableProcedure'
                     )
           AS Result;


--Function usage

SELECT *
FROM fn_NumberFunction();

SERVER=YAVUZSELIM\YAVUZSELIM

I wrote the SERVER part as above. Because that’s the name defined by me. You will be different too. If you are using Express, you can type localhost. Happened when I tried it. In general, when SQL Server is opened, you will enter the information where it says Server Name. If you don’t get it right, you won’t be able to run it.

When you run the above code block, you will see the following result.

As you can see, we have easily run the procedure that returns a table within the function.

Good luck to everyone in business and life.