Post reply

How to Design, Build and Test a Dynamic Search Stored Procedure

  • April 8, 2015 at 12:00 am

    #1789417

    pandeharsh (4/7/2015)

    Hi Dwain ,

    Very good article.

    I read the article,

    I didn't read the whole discussion that is going on.

    I have one doubts.

    I have requirement where I join table depending upon condition like in your example.

    But there is another requirement where output columns from various tables are always fixed,no matter what the where condition is.

    Only where condition are dynamic. Say @FromDate can be null or have value ,@Consignment_No can have value or it can be null.

    In this situation,dynamic Sql like in your example is good or not.

    Or simple we can write query like,

    Select t1.*,t2.*,t3.*

    From Table1 t1 inner join Table2 t2 on t1.id=t2.id

    inner join Table3 t3 on t1.col=t3.col

    where (((@FromDate is null) or (t1.FromDate>=@FromDate))

    and ((@Consignment_No is null or t2.Consignment_No=@Consignment_No)))

    Notice in my example, output column from various source are always same,only where condition are dynamic.

    In such situation,should I use dynamic query like in your exmple or my above example is ok.

    Other thing remaining constant like indexes are there in place.

    Please reply .

    I think you should read the article I linked to early by Gail Shaw on Dynamic Catch All Queries. She explains why it is better to use the method I've described. Mine was simply a how-to article showing how it could be done.

    The direct answer to your question is that in the long run, you'd probably be better off constructing the dynamic query than what you have shown. Assuming that performance is important of course.

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing post 46 (of 45 total)

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