Is there a more efficient way to do this parameter query??

  • Once again I need help.

    I need to create a report which the user can pick an operand ( greater than and less than to be exact) to run the query against.

    Example Value > 9.0 or value < 9.0.

    Is there a more efficient way of doing this? For lack of knowing any better I currently I have it set up this way:

    DECLARE @test-2 VARCHAR(100),

    DECLARE@OPERAND VARCHAR(1),

    DECLARE@VALUE VARCHAR(10),

    IF @OPERAND = '>'

    BEGIN

    SELECT DISTINCT

    C.TEST_DESC AS LAB_TEST_DESC,

    E.RESULT_DESC,

    WHERE(C.TEST_DESC = @test-2 OR @test-2 = 'ALL') AND

    CONVERT(MONEY, E.OBSERV_VALUE)>@VALUE

    END

    IF @OPERAND = '<'

    BEGIN

    SELECT DISTINCT

    C.TEST_DESC AS LAB_TEST_DESC,

    E.RESULT_DESC,

    WHERE(C.TEST_DESC = @test-2 OR @test-2 = 'ALL') AND

    CONVERT(MONEY, E.OBSERV_VALUE)>@VALUE

    END

  • SELECT

    DISTINCT C.TEST_DESC AS LAB_TEST_DESC

    ,E.RESULT_DESC

    FROM .....

    WHERE

    (C.TEST_DESC = @test-2 OR @test-2 = 'ALL')

    AND ( (CONVERT(MONEY, E.OBSERV_VALUE)>@VALUE AND @OPERAND = '>')

    OR (CONVERT(MONEY, E.OBSERV_VALUE)<@VALUE AND @OPERAND = '<'))

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Try Jason's, and also try this:

    SELECT DISTINCT -- Greater Than

    C.TEST_DESC AS LAB_TEST_DESC,

    E.RESULT_DESC

    FROM ... -- Your table here

    WHERE

    (C.TEST_DESC = @test-2 OR @test-2 = 'ALL') AND

    CONVERT(MONEY, E.OBSERV_VALUE)>@VALUE

    AND

    @OPERAND = '>'

    UNION ALL

    SELECT DISTINCT -- Less Than

    C.TEST_DESC AS LAB_TEST_DESC,

    E.RESULT_DESC

    FROM ... -- Your table here

    WHERE

    (C.TEST_DESC = @test-2 OR @test-2 = 'ALL') AND

    CONVERT(MONEY, E.OBSERV_VALUE)<@VALUE

    AND

    @OPERAND = '<'

    See which one performs better for you. Sometimes "OR" works best, sometimes "UNION" works best.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • You also need to think about the special case when E.OBSERV_VALUE = @VALUE. 😛

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • THANK YOU GUYS!!! Both ways worked great. In this case the "or" worked a little faster. But I'm printing this post for next time.

    Now can you tell me if ther is a way for Visual Studios not to refresh everytime I enter one of the parameters???? I have 6 total parameters so it's a little annoying.

  • Hmmm, not off the top of my head.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

Viewing 6 posts - 1 through 5 (of 5 total)

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