Stored Procedure Exec VS Sp_Executesql Difference

Stored procedure EXEC vs sp_executesql difference?

Your sp_executesql SQL should probably be;

DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + 
            @TableName + ' where Status=@eStatus'

This will allow you to call sp_executesql with @eStatus as a parameter instead of embedding it into the SQL. That will give the advantage that @eStatus can contain any characters and it will be properly escaped automatically by the database if required to be secure.

Contrast that to the SQL required for EXEC;

DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + 
            @TableName + ' where Status=' + char(39) + @Status + char(39)

...where a char(39) embedded in @Status will make your SQL invalid and possibly create an SQL injection possibility. For example, if @Status is set to O'Reilly, your resulting SQL would be;

select acol,bcol,ccol FROM myTable WHERE Status='O'Reilly'

Regarding EXEC, EXECUTE, SP_EXECUTE and SP_EXECUTESQL

sp_execute is depreciated and is used by OLEDB.

EXEC and EXECUTE are the same thing, one is just shorthand

Sp_executesql is similar to EXEC / EXECUTE however it accepts parameters to ensure dynamic queries are not affected by SQL injection. sp_executesql also caches the execution plan in a similar way to a stored procedure so you don't have the cost of plan compilation on every run

I would use sp_executesql in most if not all cases

Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)

sp_executesql is more likely to promote query plan reuse. When using sp_executesql, parameters are explicitly identified in the calling signature. This excellent article descibes this process.

The oft cited reference for many aspects of dynamic sql is Erland Sommarskog's must read: "The Curse and Blessings of Dynamic SQL".

SQL Server use EXEC/sp_executesql or just plain sql in stored procedure?

See no reason to use dynamic SQL here. When you do need to use dynamic SQL, you should consider sp_executesql higher in preference than EXEC(). There are a variety of reasons, including:

1. sp_executesql is more likely to reuse query plans (see Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL));

2. it is much easier to pass strongly-typed parameters into sp_executesql (thwarting SQL injection better than concatenating a string); and,

3. you can also get variables from within the dynamic SQL scope back out to the calling scope, for example:

DECLARE @i INT, @sql NVARCHAR(MAX), @dbname SYSNAME = N'model';

SET @sql = N'SELECT @i = COUNT(*) FROM ' 
    + @dbname + '.sys.tables;' 

EXEC sp_executesql @sql, N'@i INT OUTPUT', @i = @i OUTPUT;

PRINT @i;

That's not a very useful example, but it is a common problem when executing dynamic strings. But more to the point, you should only be considering dynamic SQL when you have to, not as a first resort.

Different results from executing a stored procedure using exec sp_executesql compared to EXEC

Numbers that are this different make me think that maybe it's using the default values for something? Have you taken out the defaults?

Maybe you could also add the parameters to the select, to make sure you get what you're expecting.

SELECT top 40 
    @searchLatitude,
    @searchLongitude,
    l.Id as Id,
    l.Postcode as Postcode,
    l.latitude as Latitude,
    l.longitude as Longitude,

This might aid in your debugging process.

security permission differences between select and sp_executesql within stored procedure

First is why... is there a specific rationale as to why the
permissions are implied and cascaded with one, and not the other

The reason why permissions are not required with the static SQL statement inside the stored procedure is due to ownership chaining. When all the objects involved are owned by the same user, permissions are not checked on indirectly referenced objects. Users need only execute permissions on the stored proc.

Second is... is there a work around***?

Dynamic SQL effectively breaks the ownership chain. Workarounds that do not require one grant permissions to end users on the objects include, EXECUTE AS and signing the proc with a certificate associated with a user with the needed permissions.

Below is an example of the certificate method gleaned from this tutorial in the documentation. This creates an ephemeral certificate for the permissions needed by the proc. See Erland's article for a thorough discussion of the certificate technique.

CREATE CERTIFICATE YourStoredProcedureDynamicSqlCertificate
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Provide dynamic SQL permissions';   
GO
ADD SIGNATURE TO dbo.YourStoredProcedure
   BY CERTIFICATE YourStoredProcedureDynamicSqlCertificate 
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';  
GO  
CREATE USER YourStoredProcedureDynamicSqlCertificateUser 
   FROM CERTIFICATE YourStoredProcedureDynamicSqlCertificate;  
GO  
GRANT SELECT ON dbo.services TO YourStoredProcedureDynamicSqlCertificateUser;  
GRANT SELECT ON dbo.tickets TO YourStoredProcedureDynamicSqlCertificateUser;  
GO  
ALTER CERTIFICATE YourStoredProcedureDynamicSqlCertificate
    REMOVE PRIVATE KEY;
GO

what is the difference between sp_executesql and option recompile

RECOMPILE Indicates that the Database Engine does not cache a query
plan for this procedure, forcing it to be compiled each time it is
executed. For more information regarding the reasons for forcing a
recompile, see Recompile a Stored Procedure. This option cannot be
used when FOR REPLICATION is specified or for CLR procedures.
https://msdn.microsoft.com/en-us/library/ms187926.aspx

While sp_executesql

sp_executesql can be used instead of stored procedures to execute a
Transact-SQL statement many 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.
https://msdn.microsoft.com/it-it/library/ms188001%28v=sql.120%29.aspx

---

Related Topics