sp_executeSql and EXEC/EXECUTE in SQL- Server 2005 (Difference and comparison):

Yesterday evening one of my good friend and regular reader of my blog came to my home. He has observed that I have used EXEC somewhere in my articles and sp_executeSQL somewhere in my code so we have discussed pro and cons of both. After finishing discussion he insists me to write an article for the same.

Ok then let us move towards our core topic of the article.

As per my observation, people use EXEC statement more then sp_ExecuteSQL statement. In fact, you will get good performance with sp_ExecuteSQL and cherry on the ice-cream is sp_ExecuteSQL is more secure than EXEC especially when we talk about SQL-Injection attack.

In case of direct use of T-SQL commands, SQL-Server engine will re-use query plan and won’t generate new every time. But when you are using dynamic SP to run T-SQL command, SQL Server engine may not use same query plan and may create new again and again.

Let us think that we have one Employee table in one SP named EmpDetails, if you pass empID, it will return complete detail of that employee. In this case, we may use different empID every time, though query is same just parameter is different, SQL-Server may create new execution plan every time you call your SP. So, now this is the time we have to think about performance. As long as EXEC concern, you can’t pass parameter to it but in sp_ExecuteSQL you can pass parameter so this is also one of the benefit. Let us see the use of sp_ExecuteSQL practically.

--create one table

CREATE TABLE EmpDetails

(

ID INT NOT NULL,

EmpName VARCHAR(15) NOT NULL

)

--inserting the records in it.

INSERT INTO EmpDetails

SELECT 1,'Ritesh' UNION ALL

SELECT 2,'Dharmesh' UNION ALL

SELECT 3,'Bihag' UNION ALL

SELECT 4,'Rajan'

GO

--CLEARING CACHE

DBCC FREEPOCCACHE

GO

--Example with SP_executeSQL

--note that query variable must be of

--ntext, nchar or nvarchar

DECLARE @EmpID INT

DECLARE @StrQuery NVARCHAR(500)

SELECT @StrQuery='SELECT * FROM EmpDetails WHERE ID=@eID'

EXEC SP_ExecuteSQL @StrQuery,N'@eID INT',1

EXEC SP_ExecuteSQL @StrQuery,N'@eID INT',3

--Example with EXEC

DECLARE @EmpID INT

DECLARE @StrQuery NVARCHAR(500)

SET @StrQuery='SELECT * FROM EmpDetails WHERE ID=2'

EXEC (@StrQuery)

go

DECLARE @EmpID INT

DECLARE @StrQuery NVARCHAR(500)

SET @StrQuery='SELECT * FROM EmpDetails WHERE ID=4'

EXEC (@StrQuery)

go

Reference: Ritesh Shah