locked
why to use dynamic sql??? RRS feed

  • Question

  • DECLARE @Quarter VARCHAR(50) = 'Q2'
    SELECT * FROM PropertyTotal  WHERE Quarter in

    ('''' + REPLACE((CASE @Quarter WHEN 'Q1' THEN 'Q1' WHEN 'Q2' THEN 'Q1,Q2' END),',',''',''') + '''');

     

     

    CAN SOME ONE EXPLAIN WHAT IS WRONG IN THIS query?

    WHAT IS NEED FOR ME TO GO FOR DYNAMIC SQL INORDER TO GET EXECUTE THIS QUERY????

    Monday, January 2, 2012 11:21 AM

Answers

  • Looks like you just need the following:

    SELECT * from PropertyTotal Where Quarter <= @Quarter
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, January 2, 2012 5:06 PM

All replies

  • Hi,

    Try with below query:

     

    DECLARE @Quarter VARCHAR(50) = 'Q2'
    
    SELECT * FROM PropertyTotal  WHERE Quarter in (SELECT 'Q1' UNION SELECT @Quarter)
    
    This is specific to your question here, If you can give some more details of what you are trying to achieve then people here can help with more ideas.

    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Monday, January 2, 2012 11:33 AM
  • if @Quarter value is 'Q3' then i need to get all the records in quarters Q1,Q2,Q3 .

    this should be done without using dynamic sql.

    i mean if @Quarter = 'Q1' then Quarter in q1, q2

    when @Quarter in q4 then quarter in  'q1','q2','q3','q4'  

    Monday, January 2, 2012 11:41 AM
  • Hi,

    See if this works for you:

    DECLARE @Quarter VARCHAR(50) = 'Q4'
    --SELECT * FROM PropertyTotal  WHERE Quarter in
    
    ;WITH CTE AS
    (
    	SELECT 1 Id, 'Q1' Qtr
    	UNION
    	SELECT 2, 'Q2'
    	UNION
    	SELECT 3, 'Q3'
    	UNION
    	SELECT 4, 'Q4'
    )
    
    SELECT * FROM PropertyTotal  WHERE Quarter in (SELECT Qtr FROM CTE WHERE ID <= RIGHT(@Quarter, 1))
    


     


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Monday, January 2, 2012 12:25 PM
  • WHAT IS NEED FOR ME TO GO FOR DYNAMIC SQL INORDER TO GET EXECUTE THIS QUERY????

    Hi Sudeep,

    We need dynamic  queries when we have to entertain some search criteria from UI. In other words, If we need to fetch some data from DB based on some random parameters(that do not have a defined vale for e.g a name search where user can enter just 3 characters to search) that user can choose from UI.

    In you case you need to fetch data based on the quarters. Which quarter that depends on user input. Here you can fetch data of single as well as multiple quarters.

     

    Hope I sound helpful.

    Thanks
    Every day its a new learning. Keep Learning!!
    If this post answers your question, please click Mark As Answer . If this post is helpful please click Mark as Helpful
    Monday, January 2, 2012 12:34 PM
  • You may make use of cursor to achieve this.

    Monday, January 2, 2012 4:58 PM
  • Looks like you just need the following:

    SELECT * from PropertyTotal Where Quarter <= @Quarter
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, January 2, 2012 5:06 PM
  • Hi Mitchpe,

    As per my knowledge, one should avoid using cursors. They are unnecesasarily heavy and there performance is also low.

     

    Refer this : http://dotnetpost.blogspot.com/2010/03/avoiding-cursors-in-sql-server-2005.html


    Every day its a new learning. Keep Learning!!
    If this post answers your question, please click Mark As Answer . If this post is helpful please click Mark as Helpful

    • Edited by Tiya01 Tuesday, January 3, 2012 8:38 AM
    Tuesday, January 3, 2012 4:10 AM
  • thanks Naomi.

    Tuesday, January 3, 2012 6:01 AM
  • Refer this detailed document of why we need dynamic SQL

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

    Hope this helps!

    Thanks


    Every day its a new learning. Keep Learning!!
    If this post answers your question, please click Mark As Answer . If this post is helpful please click Mark as Helpful
    Wednesday, January 4, 2012 10:36 AM