dynamic sql inside stored procedure?

  • Hi,

    Is it possible to have dynamic sql inside a stored procedure? I have a rather long query, and right now I have 3 different versions of the query in my stored procedure. The value of a variable, determines which query is used. I would like to shorten the stored procedure and be able to use just one query, but have it include or exclude various AND clauses depending on the value of the variable.

    Is this possible?

    Thanks!

  • The simple answer is yes, you can.

    The other choice you have is write the three queries in their own stored proc, and use the master proc to determine which one is called. Personally, I'd probably go this route instead of the dynamic sql.

  • Yes it is. Beware SQL injection though.

    These may be worth reading, just in general, if I understand what you're trying to do.

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/

    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
  • From the sound of it you could probably use Case statements. Here's an example:

    Declare @var int --decides which parts of where clause to use

    ,@var2 varchar(20)

    ,@var3 varchar(20)

    Select * From SomeTable

    Where Case When @var = 1 then 1 --don't check @var2

    Else Case when @var2 = col2 then 1 Else 0 End

    End = 1

    And Case When @var = 2 then 1 --don't check @var3

    Else Case when @var3 = col3 then 1 Else 0 End

    End = 1

  • If you have three distinct queries that are run, code each in its own stored procedure and use a master stored procedure to determine which of the three procedures is called.

  • Beedle (3/30/2010)


    From the sound of it you could probably use Case statements.

    You can, providing performance is not a major requirement.

    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
  • The queries are all the same except for various AND statements.

    So I guess I could do this?

    SELECT * FROM myTable

    WHERE 1 = 1

    select case @myVar

    WHEN 1 THEN

    AND myDate > 1/1/2010

    WHEN 2 THEN

    AND amount < 1000

    WHEN 3 THEN

    AND amount > 1000

    AND days < 50

    ELSE

    AND catalog = 2

  • Magy (3/30/2010)


    The queries are all the same except for various AND statements.

    So I guess I could do this?

    SELECT * FROM myTable

    WHERE 1 = 1

    select case @myVar

    WHEN 1 THEN

    AND myDate > 1/1/2010

    WHEN 2 THEN

    AND amount < 1000

    WHEN 3 THEN

    AND amount > 1000

    AND days < 50

    ELSE

    AND catalog = 2

    This will give you two result sets. Also, you might as well eliminate the where clause in this scenario - or find something to use in the cause to improve your query performance.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Oops...I definately don't want 2 resultsets!

    So I would rewrite it like this

    SELECT * FROM myTable

    Where Case

    WHEN @myVar = 1 THEN

    AND myDate > 1/1/2010

    WHEN @myVar = 2 THEN

    AND amount < 1000

    WHEN @myVar = 3 THEN

    AND amount > 1000

    AND days < 50

    ELSE

    AND catalog = 2

  • DECLARE @myVar int

    SELECT * FROM myTable

    Where Case

    WHEN @myVar = 1 THEN

    AND myDate > 1/1/2010

    WHEN @myVar = 2 THEN

    AND amount < 1000

    WHEN @myVar = 3 THEN

    AND amount > 1000

    AND days < 50

    ELSE

    AND catalog = 2

    Msg 156, Level 15, State 1, Line 7

    Incorrect syntax near the keyword 'AND'.

    You can't use the case statement like that. It can't be used with pieces of the query, only as an expression.

    This is valid

    SELECT * FROM MyTable

    WHERE

    Case @MyVar

    WHEN 1 THEN Column1

    WHEN 2 THEN Column2

    ELSE Column3

    END = 'SomeValue'

    This is also valid

    SELECT * FROM MyTable

    WHERE SomeCol = Case @MyVar

    WHEN 1 THEN 'Value1'

    WHEN 2 THEN 'Value2'

    ELSE 'Value3'

    END

    However you cannot have a case around pieces of the where clause. That requires either multiple queries (preferably in multiple procedures) as we have been advocating, or dynamic SQL.

    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
  • GilaMonster (3/30/2010)


    Beedle (3/30/2010)


    From the sound of it you could probably use Case statements.

    You can, providing performance is not a major requirement.

    If you have three distinct queries that are run, code each in its own stored procedure and use a master stored procedure to determine which of the three procedures is called.

    Basically, you have three choices.

    1. Use a case statement in the where clause. As Gail mentioned, you will have performance issues. See the links she suggests for catch-all queries and multiple-execution-paths... it just can't be described any better than what she has already done in those links.

    2. Separate stored procedures, with a master procedure determining which is called. This avoids all of the issues from #1, but adds one: identical logic in multiple procedures, where a change to the logic requires changes in multiple procedures.

    3. Dynamic sql. This avoids issues with #1 and #2, but adds the risk of sql injection. Also, if you don't parameterize the query, but instead have the text of the parameters built into the query, you will end up with sql needing to build a new plan for the query every time it is run, which can cause overall server performance issues.

    My recommendation: if the number of paths is relatively low (I use 10 or lower), I would go with #2. If > 10, I'd use #3, with a parameterized query to be able to utilize plan re-use. Yes, I would write up to 10 mostly identical procedures (plus the master), purely for overall performance reasons. I would also document inside each of them all of the other procedures that utilize the identical logic, with the note that all would need changing if the logic is changed.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • The Case statement in the where clause could cause performance issues if you're dealing with larger databases, I've been able to get away with using it more often than not however. Here's how you'd code it based on your example:

    DECLARE @myVar int

    SELECT * FROM myTable

    Where Case WHEN @myVar = 1 THEN

    Case When myDate > '1/1/2010' Then 1 Else 0 End

    WHEN @myVar = 2 THEN

    Case when amount < 1000 Then 1 Else 0 End

    WHEN @myVar = 3 THEN

    Case When amount > 1000 And days < 50 then 1 Else 0 End

    ELSE

    Case When catalog = 2 Then 1 Else 0 End

    End = 1

    You could also potentially wrap this logic in a bunch of or's, but I think this will give better performance based on tests of similar cases I've done in the past.

Viewing 12 posts - 1 through 11 (of 11 total)

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