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

Question

Post reply

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

tgonzalez

SSC Enthusiast

Points: 182
More actions
June 3, 2008 at 12:20 pm

#191849

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

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

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

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

GSquared SSC Guru Points: 260824 More actions · Answer 2

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 3

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

tgonzalez SSC Enthusiast Points: 182 More actions · Answer 4

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.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 5

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