dynamic sql query?/

• super48

  SSCrazy

  Points: 2682

  More actions

  September 2, 2013 at 12:17 am

  #297399

  where we use dynamic sql queries??

  in my office we don't use dynamic sql queries but I am want to know in which case it finds application??

  if possible give an example

  Thanks

• OTF

  SSCertifiable

  Points: 6054

  More actions

  September 2, 2013 at 6:32 am

  #1646734

  gurjer48 (9/2/2013)

  ---

  where we use dynamic sql queries??

  in my office we don't use dynamic sql queries but I am want to know in which case it finds application??

  if possible give an example

  Thanks

  http://www.sommarskog.se/dynamic_sql.html

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 2, 2013 at 7:02 am

  #1646742

  As little as possible, only where absolutely necessary and after having paid careful attention to the risks of SQL Injection.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  September 6, 2013 at 2:52 pm

  #1648249

  Erland's article, "The Curse and Blessings of Dynamic SQL" (the link that OTF inlcuded) is the best I have ever read about DSQL.

  Itzek Ben Gan's Microsoft® SQL Server 2012 T-SQL Fundamentals* includes a good summary of when Dynamic SQL can be helpful:

  Dynamic SQL is useful for several purposes, including:

  ¦ Automating administrative tasks For example, querying metadata and constructing and executing a BACKUP DATABASE statement for each database in an on-premises instance

  ¦ Improving performance of certain tasks For example, constructing parameterized ad-hoc queries that can reuse previously cached execution plans...

  ¦ Constructing elements of the code based on querying the actual data For example, constructing a PIVOT query dynamically when you don’t know ahead of time which elements should appear in the IN clause of the PIVOT operator

  * I know we are in the 2008 forum but all of the above is relevant to 2008 as well.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001