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.
|
1 2 3 4 5 6 7 |
--Executing stored procedure EXECUTE | EXEC <stored procedure name> WITH <execute_option> -- Execting string EXECUTE | EXEC ('sql string') WITH <execute_option> |
To illustrate the examples, I will create a sample stored procedure and table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations') BEGIN DROP TABLE Locations END CREATE TABLE [dbo].[Locations]( [LocationID] [int] NULL, [LocationName] [varchar](100) NULL ) GO INSERT INTO Locations values (1,'Richmond Road'),(2,'Brigade Road') ,(3,'Houston Street') GO IF EXISTS (SELECT 1 FROM SYS.procedures where name ='GetLocations') BEGIN DROP PROCEDURE GetLocations END GO CREATE PROCEDURE [GetLocations] (@LocID int) AS BEGIN select LocationID,LocationName from Locations where LocationID =@LocID END GO |
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.
|
1 |
EXEC GetLocations @LocID = 1 |
We can also assign the value returned by a stored procedure to a variable. Please refer to the following example T-SQL script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
IF EXISTS (SELECT 1 FROM SYS.procedures where name ='GetLocations') BEGIN DROP PROCEDURE GetLocations END GO CREATE PROCEDURE [GetLocations] (@LocID int) AS BEGIN declare @i int select LocationID,LocationName from Locations where LocationID =@LocID SET @I =2 RETURN @I END GO DECLARE @retunr_status int EXEC @retunr_status = GetLocations @LocID = 1 SELECT @retunr_status AS ReturnStatus |
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.
|
1 |
EXEC ('select LocationID,LocationName from locations') |
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.
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.
|
1 2 3 4 |
declare @sql varchar(max),@i int set @i =3 SET @sql ='select LocationID,LocationName from locations where LocationID = ' + cast(@i as varchar(10)) EXEC (@SQL) |
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.
|
1 |
EXEC ('select name,database_id,db_name() as CurrentDB from sys.databases where database_id <=4') at [TEST01V] |
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.
|
1 2 3 |
EXEC ('use msdb; select name,database_id,db_name() as CurrentDB from sys.databases where database_id <=4') at [TEST01V] |
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.
|
1 2 |
select name,database_id from [TEST01V].master.sys.databases where database_id <=4 |
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.
|
1 |
EXEC ('use testdb; EXEC TestProcedure') at [TEST01V] |
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.
|
1 |
EXEC [TEST01V].test.dbo.testProc |
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().
|
1 |
exec GetLocations 1 with recompile |
After executing the above T-SQL script, I executed the below script to check for the cached plan.
|
1 2 3 4 5 6 |
SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle) WHERE text LIKE '%GetLocations%' AND objtype = 'Proc' |
Please refer to the below image. Executing procedure with recompile option did not store the plan in the cache.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
exec GetLocations 1 GO SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle) WHERE text LIKE '%GetLocations%' AND objtype = 'Proc' GO exec GetLocations 1 WITH RECOMPILE GO SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle) WHERE text LIKE '%GetLocations%' AND objtype = 'Proc' GO |
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.
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
|
1 2 3 4 5 6 7 8 |
exec GetLocations 1 GO exec GetLocations 1 WITH RESULT SETS ( (ID NUMERIC(24,6),LocName varchar(50)) ) |
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.”
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
IF EXISTS (SELECT 1 FROM SYS.procedures where name ='GetLocations') BEGIN DROP PROCEDURE GetLocations END GO CREATE PROCEDURE [dbo].[GetLocations] (@LocID int) AS BEGIN select LocationID,LocationName from Locations where LocationID =@LocID select LocationID,LocationName from Locations where LocationID =@LocID END GO exec GetLocations 1 WITH RESULT SETS ( (LocID int,LocName varchar(50)), (ID NUMERIC(24,6),LocName varchar(50)) ) |
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.
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019
- Overview of the Collate SQL command - October 22, 2019
- Recover a lost SA password - September 20, 2019