We take pride in your success. We let our positivity drive us, day in and out. Talk to us at Mindfire to know us more.

Software Technology Tips

Home » Tips » Database » Advantage of  sp_executesql over Exec
Both sp_executesql and exec are used to execute Transact-SQL statement and batch. But SP_EXECUTESQL uses query execution plan, where as EXEC() compiles query every time and creates a new query execution plan.
 
 
Example:-
 
exec
DECLARE @NAME CHAR(100)
SET @NAME = 'jnana'
SET @QUERY = 'SELECT *FROM EMPLOYEE WHERE NAME = ''' + @NAME + ''''
EXEC(@QUERY)
 
SET @NAME = 'swain'
SET @QUERY = 'SELECT *FROM EMPLOYEE WHERE NAME = ''' + @NAME + ''''
EXEC(@QUERY)
 
Exec() compiles each time for new @NAME and creates a new query execution plan.
 
sp_executesql
 
Here Transact-Sql is the unicode string and unicode constants.
 
DECLARE @NAME VARCHAR(100)
 
DECLARE @QUERY1 NVARCHAR(2000)
SET @QUERY1 = N'SELECT *FROM EMPLOYEE WHERE NAME = @NAME'
 
SET @NAME = 'jnana'
EXEC SP_EXECUTESQL @QUERY1,N'@NAME CHAR(30)',@NAME
SET @NAME = 'swain'
EXEC SP_EXECUTESQL @QUERY1,N'@NAME CHAR(30)',@NAME
 
SP_EXECUTESQL compiles the first query and for each new @NAME query execution plan is reused.


Related Tags:
Database

Author: Jnana Swain

top

Database

Let us Connect!

privacy

copyright (c) Mindfire Solutions 2007-2012. Login