EXECUTE or it short form EXEC statement has several uses in SQL Server. Let us see where we could use Execute in action:
1. One of its most commonly use is to execute stored procedures
Let us first create a sample procedure to demonstrate this use
-- ===============================================
-- Sample Stored proc
-- ===============================================
CREATE PROC Sp_test_proc (@ID_Start AS BIGINT,
@ID_End AS BIGINT)
AS
BEGIN try
SELECT id,
firstname,
lastname
FROM tcontact
WHERE id BETWEEN @ID_Start AND @ID_End;
END try
BEGIN catch
SELECT Error_number(),
Error_message()
END catch
GO
To execute this procedure we could use EXECUTE or EXEC alternatively like
2. The next use of EXECUTE or EXEC is to impersonate users or logins
Let us say we have the above stored procedure and we would like to see how it executes for data_reader role members.
To demonstrate this let’s first create a login and user with a database role of data_reader
-- ===============================================
-- Create SQL Login
-- ===============================================
USE master;
GO
CREATE login testreader_login WITH password = ‘Pa$$W0rd’
GO
-- =================================================
-- Create User for TestDB Database
-- =================================================
USE testdb;
GO
-- For login OData_login, create a user in the database
CREATE USER testreader_user FOR login testreader_login WITH default_schema =
testdb
GO
If we want to impersonate execution of the stored procedure we could use EXECUTE as
— Add user to the database read role
EXEC Sp_addrolemember N’db_datareader’, N’testReader_user’
GO
But this time the stored procedure failed to execute because it user testReader_user doesn’t have enough permissions in our database to execute the stored procedure. Same is true for all users in our database that doesn’t have execute permission on the stored procedure and with only db_datareader roll in the database.
3. Querying a linked server
If one works with a distributed database environment there will be times we want to have the ability to execute SQL script on a different server instance or even a different database such as Oracle, Microsoft Access, Excel and many more. We could configure Linked Server for our Server so that we could be able to interact with other data sources.
If we have a remote server linked to the current SQL Server instance as indicated below and if we have a stored procedure called sp_ReturnContacts; we could use a four part name to query the linked server.
EXECUTE [LinkedServer Name].[Database Name].[Database Owner].[Storedprocedure name]
For example to for our example it will be as follows.
EXECUTE [testServer].testdb.dbo.Sp_returnconrtacts;
4. The other use of EXECUTE is to execute dynamic SQL generated strings
If you want to learn more about dynamic SQL please follow the following link to read more. https://blog.reckonedforce.com/dynamic-sql-in-sql-server/
