When to Use Dynamic SQL

  • We have stored procedures that hit different regions in our database and when a large change comes along it requires changes to many of them. It would be fewer, but we have been converting stored procedures from dynamic SQL to static SQL and usually this means 1 stored procedure becomes more than 1. The major change was when 1 became 22.

    I wish we could plan ahead for the changes, but often we don't know until a month or 2 before it goes live. Sometimes we have a 3 or 4 month warning, but that is rare.

    Robert Marda

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • Different regions meaning different parts of a table?

    Andy

  • sorry, meaning different databases with distinct but related purposes. One is our data on 13F filings (stocks), one is for our N30D filings (mutual funds), one for staff that buy stocks and manage mutual funds, one for staff that sell stocks. Analysts are in there somewhere too. And a few other databases.

    Robert Marda

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • Does anyone have any problems printing this article out? I'm trying to print it using IE6, but with no joy. No print job is sent to the printer. Other articles from this site print fine. Strange!

  • I would love to do with the WHERE clause what you do with the ORDER BY. Is there a way?

  • Which example are you looking?

    Robert Marda

    SQL Server will deliver its data any way you want it

    when you give your SQL Programmer enough developing time.

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • The static SQL version of the second case.

  • Forgive the delayed response.

    I think you were looking at my article called Dynamic SQL vs Static SQL part 2. That one has code in the 2nd case that uses the case function in the order by.

    In the article I wrote called How Dynamic SQL Can Be Static SQL example 1 shows how you can use the case function in the where clause.

    Robert W. Marda

    SQL Programmer

    bigdough.com

    The world’s leading capital markets contact database and software platform.

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • There is a common belief, as you mention in your article, that dynamic sql does not have compile plans. This is not true.

    Any adhoc query i.e not an SP, is parameterised, compiled and put in the cache. Depending on the complexity of the query (sub tree cost) the query will stay in the cache until it is booted out because of another query.

    the main issue is with parameterisation, it doesn't really work. It is supposed to convert values into parameters so that when you call the same query with another value you can get the same plan.

    i.e select * from mytable where col1 = 1

    and select * from mytable where col1 = 2

    should result in the same parameterised query

    select * from mytable where col1 = @p1

    Because this doesn't work you should use sp_executesql and do the parameterisation yourself

    i.e the above becomes

    sp_executesql N'select * from mytable where col1 = @p1', N'@p1 int', 1

    So if you use sp_executesql the performance will be the same as an SP, (except adhoc queries are booted out the cache before SPs).

    Articles to read on this are

    http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/default.asp

    This is detailed in depth somewhere but I can't find it at the moment.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

Viewing 10 posts - 16 through 24 (of 24 total)

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