Advanced SQL

 sp_executesql is always to be preferred for executing Dynamic SQL because, sp_executesql supports parameter substitution and also there are more chances of the reuse of execution plan when we use sp_executesql.

 

What we should remember when using sp_ExecuteSQL is that, it always executes as a separate batch irrespective if the batch/ procedure it is contained in.The transact SQL Contained in a variable which is being executed through the SP_ExecuteSQL has its Execution plan generated separately.

 

Here are a set of example on how a sp_Executesql can be used, and what are the basic mistakes that we commit while using sp_Executesql.

 

 

Here is a basic example of sp_ExecuteSQL, where we declare a variable and assign a valid sql to it and when executed it throws an error “Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar‘.”

 

This tells us that, it is compulsory to use variable of the above data types.

Use AdventureWorks
GO
DECLARE @SqlStr varchar(500)
SET @SqlStr='SELECT TOP 10 EmployeeID,NationalIDNumber,Title FROM HumanResources.Employee'
EXEC sp_executesql @SqlStr
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Using sp_ExecuteSQL, sp_executesql error - Parsed and Compiled

 

Now, lets make another mistake deliberately to see what happens if we use an invalid sql statement in the variable which is being executed through sp_ExecuteSQL, it tells us that the sql query in the variable is parsed and compiled only when executed through the sp_ExcuteSQL

Use AdventureWorks
GO
DECLARE @SqlStr nvarchar(500)
SET @SqlStr='SELECT TOP 10 EmployeeID,A_NationalIDNumber,Title FROM HumanResources.Employee'
EXEC sp_executesql @SqlStr
Msg 207, Level 16, State 1, Line 1
Invalid column name 'A_NationalIDNumber'.

 

 

 

Now, lets go and put correct sql in the variable and run it using sp_ExecuteSQL to see how it works. 

Use AdventureWorks
GO
DECLARE @SqlStr nvarchar(500)
SET @SqlStr='SELECT TOP 10 EmployeeID,NationalIDNumber,Title FROM HumanResources.Employee'
EXEC sp_executesql @SqlStr

 

The below code snippet shows how a string can be formed dynamically and then be assigned to a variable which is in turn executed through the sp_ExecuteSQL stored procedure

Use AdventureWorks
GO
DECLARE @Employeeid int
DECLARE @SqlStr nvarchar(500)
SET @EmployeeID=10
SET @SqlStr='SELECT TOP 10 EmployeeID,NationalIDNumber,Title FROM HumanResources.Employee WHERE EmployeeID= ' + Cast(@Employeeid as Varchar)
EXEC sp_executesql @SqlStr

 

This example, shows how a result set that is being returned via sp_ExecuteSQL be stored in a temporary table.

USE AdventureWorks
GO
CREATE TABLE #usp_TestInsertWithSp_exec(EmployeeID int, NationalIDNumber nvarchar(15),Title nvarchar(50))
DECLARE @Employeeid int
DECLARE @SqlStr nvarchar(500)
SET @EmployeeID=10
SET @SqlStr='SELECT TOP 10 EmployeeID,NationalIDNumber,Title FROM HumanResources.Employee WHERE EmployeeID= ' + Cast(@Employeeid as Varchar)
INSERT INTO #usp_TestInsertWithSp_exec
EXEC sp_executesql @SqlStr
SELECT * FROM #usp_TestInsertWithSp_exec
DROP TABLE #usp_TestInsertWithSp_exec

 

The below code gives a basic example of How to specify output parameters when we use the sp_executesql stored procedure in SQL Server. 

Use AdventureWorks
GO
DECLARE @Employeeid int
DECLARE @NationalID nvarchar(15)
DECLARE @SqlStr nvarchar(500)
SET @EmployeeID=10
SET @SqlStr='SELECT @NationalID= NationalIDNumber FROM HumanResources.Employee WHERE EmployeeID= ' + Cast(@Employeeid as Varchar)+''
EXEC sp_executesql @SqlStr, N'@NationalID nvarchar(15) OUTPUT',@NationalID OUTPUT
PRINT @NationalID

I suppose, that I have covered most of the points on SP_ExecuteSQL, if you have any comments regarding this sp_ExecuteSQL topic or any other topic on SQL Server, Please feel free to comment.

If you have liked the article and want to get the articles (or) new posts at sql like, subscribe to articles through email by entering your email address in the top right and verifying the same.