Jignesh Raiyani
Calling SQL Server Procedure inside the Function

Execute a SQL Server procedure inside a function

June 23, 2020 by

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:

OPENROWSET() Query to calling a SQL Server Procedure inside the Function

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:

Function:

Execution:

Calling SQL Server Procedure inside the Function

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:

Function:

Execution:

Calling SQL Server Procedure inside the Function

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:

Execution:

Error on OPENROWSET() query for Calling a SQL Server Procedure inside the Function

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.

Jignesh Raiyani
222 Views