Post reply

Parameterized SQL vs Dynamic SQL

  • January 24, 2012 at 2:09 pm

    #143091

    A)What is the difference between Parameterized SQL and Dynamic SQL?

    B) If Parameterized SQL is executed using sp_executesql, does query becomes parameterized dynamic SQL?

  • January 24, 2012 at 2:54 pm

    #1437981

    The difference is huge. Dynamic sql is simply building a executing against your database. This is open to sql injection. Parameterized query is using variable to hold the values for your query. The big advantage is the query engine will use the supplied value as the datatype and as such is not prone to sql injection.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • January 24, 2012 at 6:09 pm

    #1438051

    Thanks a lot. Is it possible for you to give me one example of each? Thanks

  • January 24, 2012 at 6:46 pm

    #1438057

    Kal Penn (1/24/2012)

    Thanks a lot. Is it possible for you to give me one example of each? Thanks

    Sure:

    DECLARE @parameter integer = 1;

    -- Dynamic SQL

    DECLARE @sql nvarchar(max) =

    N'

    SELECT *

    FROM AdventureWorks2008R2.Production.Product AS p

    WHERE p.ProductID = ' + CONVERT(varchar(12), @parameter);

    EXECUTE (@sql);

    -- Parameterized dynamic SQL

    EXECUTE sys.sp_executesql

    @statement =

    N'

    SELECT *

    FROM AdventureWorks2008R2.Production.Product AS p

    WHERE p.ProductID = @ProductID;',

    @params = N'@ProductID integer',

    @ProductID = @parameter;

    The first example is much less safe because you don't have as much control over the string that is executed.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • January 24, 2012 at 9:31 pm

    #1438077

    Thanks Paul,

    So in your second SQL(Dynamic Parameterized SQL), since variable is not concatenated in WHERE condition (WHERE p.ProductID = @ProductID) is this what makes it 'Parameterized SQL'?

  • January 24, 2012 at 10:18 pm

    #1438082

    Kal Penn (1/24/2012)

    Thanks Paul,

    So in your second SQL(Dynamic Parameterized SQL), since variable is not concatenated in WHERE condition (WHERE p.ProductID = @ProductID) is this what makes it 'Parameterized SQL'?

    Explicitly defining a parameter is what makes the sp_executesql call parameterized 🙂

    The concatenated string is executed as-is; it has no parameters.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • January 27, 2012 at 7:42 am

    #1439370

    So, use of Parameter in sp_executesql makes it parameterized

    (defining parameter) @params = N'@ProductID integer', (value of the parameter) @ProductID = @parameter;

    If only SQL statement was used, then it would be just dynamic SQL

    Thanks, got it. Will stick in my skull now.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply