SQL SERVER – 2005 Comparison SP_EXECUTESQL vs EXECUTE/EXEC

Common Properties of SP_EXECUTESQL and EXECUTE/EXEC

  • The Transact-SQL statements in the sp_executesql or EXECUTE string are not compiled into an execution plan until sp_executesql or the EXECUTE statement are executed. The strings are not parsed or checked for errors until they are executed. The names referenced in the strings are not resolved until they are executed.
  • The Transact-SQL statements in the executed string do not have access to any of the variables declared in the batch that contains thesp_executesql or EXECUTE statement. The batch containing the sp_executesql or EXECUTE statement does not have access to variables or local cursors defined in the executed string.
  • If the executed string has a USE statement that changes the database context, the change to the database context only lasts untilsp_executesql or the EXECUTE statement completes.

Comparison SP_EXECUTESQL vs EXECUTE/EXEC

sp_executesql gives you the possibility to use parameterised statements, EXECUTE does not. Parameterised statements gives no risk to SQL injection and also gives advantage of cached query plan. The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve readability of your code when there are many parameters are used. When you use thesp_executesql stored procedure to executes a Transact-SQL statements that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.

Solarwinds

sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

Use SP_EXECUTESQL rather than EXEC(), it has better performance and improved security.

The syntax for sp_executesql for SQL Server 2005 is
sp_executesql [ @stmt = ] stmt
[
{, [@params=]
N'@parameter_name data_type [ OUT | OUTPUT ][,...n]' }
{
, [ @param1 = ] 'value1' [ ,...n ] }
]

The size of the string is limited only by available database server memory. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Explanation of WITH ENCRYPTION clause for Stored Procedure and User Defined Functions
Next Post
SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable

Related Posts

46 Comments. Leave new

  • Good article.
    Dynamic SQL is very useful stuff, but we should use it carefully. Here is an example of dynamic SQL and SQL injection

    Regards

    Reply
  • Hello Pinal

    Here is one of the issue that I have now….

    I am executing a query in MS Access wih a where clause referring to the PK in the SQL Server table .

    See the query below …

    tab b is local table ( stored in MS Access ) has few thousand records . tab a is a linked table referring to a table in MS SQL ( has 70+ million records) .

    cola is PK in both tables.

    Select a.cola , a.colb from taba a , tab b

    where a.cola = b.cola

    When I look up in Activity Monitor , I see lot of process running on behalf of this query . When I see trace in the SQL Server Profiler , I see the events as

    RPC:Completed … exec sp_execute 1 , 101

    RPC:Completed … exec sp_execute 1 , 102

    RPC:Completed … exec sp_execute 1 , 104

    RPC:Completed … exec sp_execute 1 , 105

    Here the second argument to this proc ( sp_execute ) are the values of cola in local table .

    Why would MS SQL server / ODBC would translate a simple query into mutiple sp_execute calls ( in this case 70 million calls) ?

    I am on SQL Server 2008 R2 and MS Access 2007

    Have you come across this situation before ? Please share your experience.

    Reply
  • I need to access different databases from a single function how can i do it with dynamic function.
    if it is not possible kindly suggest other alternatives.

    Reply
  • ALTER PROCEDURE [dbo].[USP_TESTSP]

    AS
    BEGIN
    DECLARE @SQL nvarchar(max),@TABLENAME sysname,@id varchar(max)
    SELECT @TABLENAME=’Antenatal’
    select @SQL= ‘select @id=MemberID FROM’+’ ‘+@TABLENAME+’ ‘+’where TrId=’+’ALV11091′
    exec sp_executesql @SQL,’@id varchar(max)output’,@id=@id output

    END

    But I am getting an error as
    Procedure expects parameter ‘@parameters’ of type ‘ntext/nchar/nvarchar’.

    Reply
  • declare @stmt varchar(255)

    SET @stmt = SELECT output FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
    ‘Excel 12.0;Database=C:\New.xlsx’,
    ‘select * from [sheet1$]’)
    exec(@stmt)

    I am executing above code and getting error in valid systanx neat SELECT.
    output of select statement is
    CREATE TABLE dbo.Users (
    Id int identity(1,1) ,
    Username varchar (55) NULL ,
    FirstName varchar (55) NULL ,
    LastName char (55) NULL ,
    City varchar (55) NULL
    )
    GO

    So basically concept is to create a table from excel. Please let me know what is wrong , also suggest if there are any other way to do this.

    Reply

Leave a Reply

Menu