September 18, 2008 at 10:31 am
Hi all,
Let's say I have an Orders table with rows identified as open or closed depending on the value of the OpenOrder field -- 0 means closed, and 1 means open. Please see code below:
DECLARE @Orders TABLE
(
OrderDescription varchar(6),
OpenOrder int
)
DECLARE @Parameter int
SET @Parameter = 0
-- SET @Parameter = 1
INSERT INTO @Orders VALUES('Closed',0)
INSERT INTO @Orders VALUES('Open',1)
SELECT *
FROM @Orders
WHERE OpenOrder = @Parameter
As you can see, I am passing in a parameter which will determine whether the query returns open or closed orders. The parameter is coming from Crystal Reports but that's probably irrelevant. My question is how can I modify this so that I can pass in a third value -- lets say a 3 -- and return all the rows (open and closed)?
TIA
Ahmet
September 18, 2008 at 12:46 pm
Use NULL as your flag value, like this:
DECLARE @Orders TABLE
(
OrderDescription varchar(6),
OpenOrder int
)
DECLARE @Parameter int
SET @Parameter = 0
-- SET @Parameter = 1
INSERT INTO @Orders VALUES('Closed',0)
INSERT INTO @Orders VALUES('Open',1)
SELECT *
FROM @Orders
WHERE OpenOrder = @Parameter OR @Parameter IS NULL
The OR clause at the end can lead to performance problems sometimes, however it should have virtually no adverse impact on a test with such low specificity.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 23, 2008 at 1:23 pm
Thanks for your response!
Does anyone know how to pass a null value from a Crystal Reports parameter into an SQL query? I can pass numeric values but not sure how to pass NULL.
TIA
Ahmet
September 23, 2008 at 2:01 pm
If you cannot figure out how to pass in a Null value - then, pick a value that will never exist in the data. I would probably go with a negative number like -1. Then, the statement becomes:
DECLARE @Orders TABLE
(
OrderDescription varchar(6),
OpenOrder int
DECLARE @Parameter int
SET @Parameter = 0
-- SET @Parameter = 1
INSERT INTO @Orders VALUES('Closed',0)
INSERT INTO @Orders VALUES('Open',1)
SELECT *
FROM @Orders
WHERE OpenOrder = @Parameter OR @Parameter = -1
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 24, 2008 at 5:50 am
Excellent. Now I get it.
Thank you both.
September 24, 2008 at 10:58 am
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 26, 2008 at 10:07 am
Another option that I got from a search some time ago is to use a Case statement in the Where section. Based on your code so far, add a set of Case statements as such, where you pass 2 for All.
Where (Case When @Parameter = 2 Then 1 Else OpenOrder End) =
(Case When @Parameter = 2 Then 1 Else @Parameter End)
When @Parameter is 2, being passed from Crystal to mean you want all of them, each Case statement will resolve to the number 1, meaning 1 = 1, which means True (nothing will be filtered => return all). When @Parameter is anything else, it will compare its value to the database field, returning either Open or Closed, depending upon the value.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply