Post reply

skip condition in "where" inside dataset when param is not submited

  • February 9, 2017 at 9:58 am

    #335137

    Hi,
    example
    in report dataset  query defined as

    select * from sysobjects where name like = @Name or type =@Type

    if user does not pass @name or @Type can we in query designer  modify it similar  something like
    select * from sysobjects where
    if (@name is not null)
    begin
       name     like = @Name or
    end
    if(@type is not null)
    begin
          type =@Type
    end
    if @name is null and @type is null
    begin
     select * from sysbojects
    end

    I don't want to create stored procedure, build dynamic sql    add logic based on paramaters of sp
    Thank you  

  • February 9, 2017 at 10:03 am

    #1927579

    You can do it like that:


    select * from sysobjects where (name like = @Name OR @Name is null) or (type =@Type OR @Type is null)

     Notice that queries like that tend to have poor query plan and slow execution.  It is better to use the logic that in your question you wrote that you don't want to use.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • February 9, 2017 at 12:04 pm

    #1927619

    Thank a lot .
    Provided solution is working fine

  • February 9, 2017 at 12:12 pm

    #1927621

    ebooklub - Thursday, February 9, 2017 12:04 PM

    Thank a lot .
    Provided solution is working fine

    Think so? Please read the following article on "Catch-All Queries" and see how future performance will tank and what you can do about it.  The article should be required reading for all Developers.
    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

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