Parameterized SQL vs Dynamic SQL

Question

Parameterized SQL vs Dynamic SQL

Kal Penn

Ten Centuries

Points: 1273
More actions
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?

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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/

Kal Penn Ten Centuries Points: 1273 More actions · Answer 2

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

Paul White SSC Guru Points: 150467 More actions · Answer 3

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

Kal Penn Ten Centuries Points: 1273 More actions · Answer 4

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'?

Paul White SSC Guru Points: 150467 More actions · Answer 5

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

Kal Penn Ten Centuries Points: 1273 More actions · Answer 6

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.