June 3, 2008 at 12:20 pm
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
June 3, 2008 at 12:38 pm
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. SelburgJune 3, 2008 at 12:46 pm
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
June 3, 2008 at 12:51 pm
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. SelburgJune 3, 2008 at 1:20 pm
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.
June 3, 2008 at 1:23 pm
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. SelburgViewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply