Procédure stockée EXEC vs sp_executesql différence?

J'ai écrit deux procédure stockée avec un sp_executesql et d'autres n'ont pas sp_executesql
les deux sont en cours d'exécution correctement mêmes résultats, je n'ai pas quelle est la différence entre

EXEC (@SQL) vs EXEC sp_executesql @SQL, N'@eStatus de type varchar(12)',
@eStatus = @Status

et Comment EXEC(@SQL) est sujette à l'injection SQL et sp_executesql @SQL...... n'est-ce pas?

Ci-dessous la Procédure Stockée sans sp_executesql

ALTER proc USP_GetEmpByStatus
(
@Status varchar(12)
)
AS
BEGIN
DECLARE @TableName AS sysname = 'Employee'
Declare @Columns as sysname = '*'
DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + @TableName + ' where Status=' + char(39) + @Status + char(39)
print (@SQL)
EXEC (@SQL)
END

EXEC USP_GetEmpByStatus 'Active'

Ci-dessous la procédure stockée avec sp_executesql 

create proc USP_GetEmpByStatusWithSpExcute
(
@Status varchar(12)
)
AS
BEGIN
DECLARE @TableName AS sysname = 'JProCo.dbo.Employee'
Declare @Columns as sysname = '*'
DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + @TableName + ' where Status=' + char(39) + @Status + char(39)
print @SQL
exec sp_executesql @SQL, N'@eStatus varchar(12)', @eStatus = @Status
END

EXEC USP_GetEmpByStatusWithSpExcute 'Active'
InformationsquelleAutor Registered User | 2013-02-06
  1. 18

    Votre sp_executesql SQL devrait probablement être;

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

    Cela vous permettra d'appeler sp_executesql avec @eStatus en tant que paramètre au lieu de l'intégrer dans le SQL. Qui vous donnera l'avantage que @eStatus peut contenir n'importe quel caractère et il va être échappées automatiquement par la base de données, si nécessaire, pour être sûr.

    Contraste que le SQL nécessaire pour EXEC;

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

    ...où un char(39) incorporés dans @Statut feront de votre SQL invalide et éventuellement de créer une injection SQL possibilité. Par exemple, si @Statut est O'Reilly, votre SQL serait;

    select acol,bcol,ccol FROM myTable WHERE Status='O'Reilly'
    • Pourquoi la clause where n'est pas clos, avec '?
    • Si vous placez @eStatus avec des guillemets, il va utiliser le string '@eStatus' rechercher dans la base de données, si vous utilisez un non cotées @eStatus, le valeur sera utilisé. Par exemple, si @eStatus est "test", un INSERT INTO myTable VALUES (@eStatus) insérer la chaîne "test", tandis que INSERT INTO myTable VALUES ('@eStatus') insérer la chaîne de caractères '@eStatus'.
    • Je pense qu'il manque une fermeture ' dans le premier exemple de code.
    • l'autre différence, c'est quand vous utilisez des tables temporaires dans le sql dynamique , sp_executesql utilise le plan de cache où, comme exec ne l'utilise pas
    InformationsquelleAutor Joachim Isaksson
  2. 19

    En plus de l'utilisation, il y a quelques différences importantes:

    1. sp_executesql permet de faire des déclarations paramétrable
      Par conséquent, Il est plus sûr que EXEC en termes d'injection SQL

    2. sp_executesql pouvez tirer parti de la mise en cache de requêtes.
      Le TSQL chaîne est créée une seule fois, après que chaque fois que la même requête est appelée avec sp_executesql, SQL Server extrait du plan de requête à partir du cache et le réutilise

    3. Tables temporaires créées dans EXEC ne pouvez pas utiliser la table temporaire de mécanisme de mise en cache

    InformationsquelleAutor FLICKER
  3. 4

    Avec sp_executesql, vous n'avez pas à construire votre requête comme ça. Vous pouvez déclarer comme ceci:

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

    De cette façon, si votre @Status valeur est à partir d'un utilisateur vous pouvez utiliser @eStatus et ne pas avoir à s'inquiéter au sujet d'échapper à '. sp_executesql vous donne la possibilité de mettre des variables dans votre requête dans la forme d'une chaîne, au lieu d'utiliser la concaténation. Si vous avez un souci en moins.

    La colonne et les variables de table sont toujours les mêmes, mais c'est moins susceptibles d'être directement à partir d'un utilisateur.

    InformationsquelleAutor JeffB
  4. 2

    Avec Exec Vous ne pouvez pas avoir un place de titulaire dans votre instruction T-Sql chaîne.

    sp_executesql
    vous donne l'avantage d'avoir une place de titulaire et passer à la valeur réelle à runtime

    InformationsquelleAutor Mohammadreza