Ranga Babu
EXEC SQL example with recompile

EXEC SQL overview and examples

June 18, 2019 by

In this article, we will review on EXEC SQL statement in SQL Server and explore a few examples.

The EXEC command is used to execute a stored procedure, or a SQL string passed to it. You can also use full command EXECUTE which is the same as EXEC.

Syntax of EXEC command in SQL Server

Following is the basic syntax of EXEC command in SQL Server.

To illustrate the examples, I will create a sample stored procedure and table.

Executing a stored procedure

To execute a stored procedure using EXEC pass the procedure name and parameters if any. Please refer to the below T-SQL script to execute a stored procedure.


EXEC SQL example of stored procedure

We can also assign the value returned by a stored procedure to a variable. Please refer to the following example T-SQL script.


assigning value returned from procedure to a variable using EXECUTE SQL

Executing string

To execute a string, construct the string and pass it to the EXEC SQL command. Please refer to the below example which executes a string.


EXECUTE statement with string example

Following is the example of using EXEC with string constructed from a variable. You always need to enclose the string in the brackets else execute statement consider it as a stored procedure and throws an error as shown in the below image.

EXEC SQL with variable example

Constructing a string from the variable and executing it using EXEC SQL command may inject unwanted code. There are some techniques to avoid SQL injection. We will review those techniques in another article.


EXECUTE string using variable example

Executing queries on a remote server

AT linked_server_name clause along with EXEC command is used to execute queries on a remote server. A linked server must be configured and RPC Out option must be enabled on the linked server to execute queries on a remote server.

Please refer to the following example of executing a query on a remote server. Replace the linked server name with your linked server name.


EXEC SQL query on linked server

If we do not specify the database name, EXEC SQL statement will execute the query on the default database of the login used in the linked server.

If you want to execute query in a specific database use “USE databasename” in the query. Please refer to the below example.


EXEC query on linked server

We can also issue a select query against the remote server using four-part notation. We must enable the Data Access option on the linked server. Please refer to the below example.

To execute a stored procedure on a remote server, use below T-SQL script by replacing the linked server name, database name, and the stored procedure name.

Following is the example of executing a stored procedure on the linked server using four-part notation. Here “TEST01V” is the server name, “test” is the database name, and “dbo” is the schema name.

EXEC WITH RECOMPILE

This execution option in EXEC SQL statement creates a new plan and discards it after using it. If there is an existing plan for the procedure it remains the same in the cache. If there is no existing plan for the procedure and using with recompile option will not store the plan in cache.

Please refer to the below example for executing the procedure with recompile option. Before executing this I have cleared the plan cache using DBCC FREEPROCCACHE().

After executing the above T-SQL script, I executed the below script to check for the cached plan.

Please refer to the below image. Executing procedure with recompile option did not store the plan in the cache.

EXEC SQL example with recompile

Now, we will execute procedure without recompile which will save the execution plan in cache and after that, we will execute the procedure with recompile option to see if the existing plan is changed or not.

Please refer to the below image for the result set of the above query. We can see the plan identifier and use counts are the same and the existing plan did not change. EXEC WITH RECOMPILE did not use the existing plan in the cache and created a new plan, used it and discarded it.

plan handle with recompile

EXECUTE WITH RESULT SETS

This option is used to modify the result set of a stored procedure or the string executed as per the definition specified in the WITH RESULT SETS clause.

Please refer to the following example of executing a stored procedure with RESULT SETS


EXEC SQL example with result sets

We can modify the result set headers and the data type of the column return by executing the stored procedure. This is like using convert (), cast () and column aliases in the normal T-SQL script.

If the procedure or T-SQL string returns more than one result set we must define multiple results sets in the WITH RESULTS SETS clause as well else it will throw following error “EXECUTE statement failed because it’s WITH RESULT SETS clause specified 1 result set(s), and the statement tried to send more result sets than this.”

multiple result set in EXECUTE statement

Please refer to the following example to use WITH RESULTS SETS clause in EXEC SQL statement for multiple results sets returned by stored procedure or string. In this example, the stored procedure returns two result sets which are the same. I have defined two results in WITH RESULTS SETS clause by changing the datatype and result set headers in both result sets.

EXECUTE statement with multiple result set in

Conclusion

We explored different aspects of EXEC SQL Statement with several examples in this article. In case you have any questions, please feel free to ask in the comment section below.

Ranga Babu

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies
Ranga Babu

Latest posts by Ranga Babu (see all)

2,733 Views