In this article, we will ensure that the execution of the SQL Server procedure inside the function is possible. A common understanding of the database developer is that the procedure cannot be utilized or executed inside a user-defined function because the user-defined function does not allow performing DML operation with the table. The procedure is allowed to play with the database and its property, whether a user-defined function isn’t. That should be the essential reason behind allowing the execution of a SQL Server procedure inside the function.
Let’s talk about different types of functions in SQL Server. The essential two types of SQL Server functions are user-defined function and system-defined function. User-defined functions can be defined by two types; scalar function and tabular values function. Ideally, tabular function and procedure are similar in terms of data fetching result set only. A procedure and function both can be defined with parameters and return the result set with data manipulation with multiple tables as required. But the restriction with the function is that function cannot performs any ALTER, INSERT, UPDATE or DELETE operation on the table except temp and variable tables. The following structure difference between a procedure and tabular function is that function must be required to mention RETURNS with the output type, and it ends with the RETURN keyword that is not needed for the SQL Server procedure.
The primary purpose of a tabular function is an alternative of view, yet as an add-on with function is that the user can write the logic in the function with the help of a temp table and table variable for more data processing where a view isn’t. Functions and views are both helpful in terms of use cases for business logic and requirement side as well as the performance side.
According to Microsoft standard, stored procedures cannot be executed inside the function, but technically it is possible with some tweaks. How does a user require to use a procedure in the function? This is happening when a function is called by the database, and the user wants to perform an INSERT, UPDATE, or DELETE operation within that function task. Before proceeding with this approach, a user should be clear with the purpose of procedure and function and their differences and limitations.
Procedure
The stored procedure is a database program that can be utilized to perform CRUD tasks with the table. It requires SQL Server to compose the business rationale with the different tables with information manipulation. Essential standard conventions of comparing a procedure with functions are listed below:
- SQL Server Database Engine will create an execution plan with the procedure’s first execution
- A procedure is a pre-compiled code which will be run on the execution plan with different parameters
- Multiple result sets are supported with single procedure execution
- A procedure can return the out parameter
- A procedure can call a function inside the program’s body
Function
SQL Server tabular function is a program that can be used to return data by joining the multiple tables. Essential standard conventions of comparing a function with the procedure are listed below;
- The function is used to reckon the data from single or multiple tables and must return a result set
- A function is a real-time compilation program
- A function does not allow to perform any ALTER, CREATE, INSERT, UPDATE, or DELETE operation with the user table except the temp table and variable table
- A function generates run time execution plan
- A function does not support the OUTPUT parameter
- A function cannot call the procedure inside the program’s body
A procedure can be executed inside the function with the help of OPENROWSET() using OLE DB provider connection MSDASQL. Users can define the OPENROWSET() connection with the necessary details of SQL Server instance with declared Linked Server and credentials that has access to the database. Here, we will have an error handling and different scenario with INSERT and SELECTS operation in the procedure and calling it by SQL Server function. Let’s start by calling a procedure with the OPENROWSET T-SQL statement.
Executing a SQL Server Procedure inside the Function
MSDASQL connection is established with the local SQL Server instance in the OPENROWSET statement. The server is set to localhost and then we have Trusted_Connection=yes for making your trusted connection, which does not require credentials to connect your database for MSDASQL connection:
|
1 2 |
SELECT * FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=localhost; Trusted_Connection=yes', 'EXEC AdventureWorks..[usp_get]'); |
The usp_get procedure is used in the above sample code that exists in the AdventureWorks database. If you do not specify the database in connection and query statement, then the query will return an error as below:
OLE DB provider “MSDASQL” for linked server “(null)” returned message “[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure ‘usp_get’.”.
Msg 7350, Level 16, State 2, Line 11
Cannot get the column information from OLE DB provider “MSDASQL” for linked server “(null)”.
Fetch and insert operation with executing SQL Server procedure inside the function
Procedure usp_get is returning data from the user-defined table and calling it inside the function with OPENROWSET().
Procedure:
|
1 2 3 4 5 6 7 8 |
CREATE PROCEDURE [usp_get] AS BEGIN SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM counter_ END |
Function:
|
1 2 3 4 5 6 |
CREATE FUNCTION [fn_getusp]() RETURNS TABLE AS RETURN SELECT * FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=localhost; Trusted_Connection=yes', 'EXEC AdventureWorks..[usp_get]') AS a; |
Execution:
|
1 2 |
SELECT * FROM [fn_getusp]() |
Here, the procedure’s result set is returned by the function. Users can insert those rows into the temp table or table variable to process it further. Now, let’s have the same scenario with inserting data into the user table in the procedure and calling it within a function.
Procedure:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE PROCEDURE [usp_insert] AS BEGIN SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED INSERT INTO counter_(id) SELECT 1 SELECT 1 as res END |
Function:
|
1 2 3 4 5 6 |
CREATE FUNCTION [fn_InsertUsp]() RETURNS TABLE AS RETURN SELECT * FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=localhost; Trusted_Connection=yes', 'EXEC AdventureWorks..[usp_insert]') AS a; |
Execution:
|
1 2 |
SELECT * FROM fn_InsertUsp() |
Here, procedure [usp_insert] is inserting rows into the user-defined table and returning the result set as output. Because procedure execution in the OPENROWSET query must require an output, either INSERT, UPDATE, DELETE, or SELECT operation is within the procedure code. If you do not return the output in the procedure, then OPENROWSET() query will return an error as below:
Msg 7357, Level 16, State 1, Line 11
Cannot process the object “EXEC Adventureworks..[usp_insert]”. The OLE DB provider “MSDASQL” for linked server “(null)” indicates that either the object has no columns or the current user does not have permissions on that object.
As an example, we can replicate this error with the OPENROWSET statement outside the function as well:
Procedure:
|
1 2 3 4 5 6 7 8 9 |
ALTER PROCEDURE [usp_insert] AS BEGIN SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED INSERT INTO counter_(id) SELECT 1 END |
Execution:
|
1 2 |
SELECT * FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=localhost; Trusted_Connection=yes', 'EXEC AdventureWorks..[usp_insert]') AS a; |
Error message:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object “EXEC AdventureWorks..[usp_insert]”. The OLE DB provider “MSDASQL” for linked server “(null)” indicates
that either the object has no columns or the current user does not have permissions on that object.
The above samples include the number of scenarios of procedure integration with the function, including error handling. Now, it turns out to be simpler for the users who want to include this methodology in the existing database structure. In all of the above sample codes, the SQL Server instance name is used as a localhost because it performed each task on the local machine. However, it should be the instance name of the SQL Server. If users want to execute the procedure on a remote server, then remote SQL Server name can be used instead of the localhost and that remote SQL Server instance name must exist in your linked servers list.
The use of procedures inside the function could be required when users are integrating the third-party application in the database. Here’s one use case, a user wants to include some INSERT, UPDATE or DELETE operation; however, the user wants to roll out any changes in the back end application code and most noticeably awful circumstances could be the back end application calling the function. On the execution of a function, a user wants to add any INSERT, UPDATE or DELETE operation on a local or remote SQL Server instance. This requirement cannot be directly dealt with by the function, and direct use of the SQL Server procedure inside the function is not a standard solution for the same. In that case, a user can apply this strategy to accomplish the goal.
If you make any improvements in a backend application of a third-party application, the user must be dependent on that application version and causes more user dependency as well. If we go with the database changes, a database administrator has to just modify the function only with procedure integration. However, it is like a reliance on database administrators because anybody has to compare that database function and relevant table schema as well.
Conclusion
Calling a SQL Server procedure inside the function is recommended when you don’t have any substitute alternate option to achieve the INSERT, UPDATE or DELETE operation without the procedure. You cannot use this approach in the general cases because it raises a lot of troubleshooting difficulties, and it’s not the recommended method by Microsoft too.
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server - July 17, 2020
- How to automate Table Partitioning in SQL Server - July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020