December 27, 2009 at 3:37 pm
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sprocGefilterdeList]
(
@toestand int,
@goedkeuringAanvragen int
)
AS
SELECT
a.Naam , a.Voornaam , a.Dienst,c.auteur,b.bestellingdatum, b.bestellingid,
b.bestellingdatum, b.bestellingid,c.Titel,c.BestellingItemid,c.Toestand,c.Goedkeuringaanvragen From Aanvrager AS a INNER JOIN Bestelling AS b
ON a.Id_aanvrager=b.Id_aanvrager INNER JOIN BestellingItem c
ON b.bestellingid=c.bestellingid
WHERE c.Toestand = CASE
WHEN @toestand <> -1 THEN @toestand
END
-- AND WHERE c.Goedkeuringaanvragen = CASE
--WHEN @goedkeuringAanvragen <> -1 THEN @goedkeuringAanvragenEND
ORDER BY c.BestellingItemid DESC
I am unable to add a second Where condition... Whats the correct syntax for this??
And another important problem is:
if my @toestand parameter value is '-1' then it shouldnt set a filter on c.Toestand but i dont know how i can do this
December 27, 2009 at 7:21 pm
You don't have to set a filter. I would do it like this:
SELECT
a.Naam, a.Voornaam, a.Dienst, c.auteur, b.bestellingdatum, b.bestellingid,
b.bestellingdatum, b.bestellingid, c.Titel, c.BestellingItemid,
Toestand =
CASE
WHEN @toestand <> -1 THEN @toestand
ELSE c.Toestand
END,
Goedkeuringaanvragen =
CASE
WHEN @goedkeuringAanvragen <> -1 THEN @goedkeuringAanvragen
ELSE c.Goedkeuringaanvragen
END
FROM
Aanvrager AS a
INNER JOIN Bestelling AS b
ON a.Id_aanvrager=b.Id_aanvrager
INNER JOIN BestellingItem c
ON b.bestellingid=c.bestellingid
ORDER BY c.BestellingItemid DESC
December 28, 2009 at 8:53 am
I think you want this:SELECT
a.Naam,
a.Voornaam,
a.Dienst,
c.auteur,
b.bestellingdatum,
b.bestellingid,
b.bestellingdatum,
b.bestellingid,
c.Titel,
c.BestellingItemid,
c.Toestand,
c.Goedkeuringaanvragen
From
Aanvrager AS a INNER JOIN
Bestelling AS b
ON a.Id_aanvrager = b.Id_aanvrager INNER JOIN
BestellingItem c
ON b.bestellingid = c.bestellingid
WHERE
c.Toestand = CASE
WHEN @toestand <> -1 THEN @toestand
ELSE c.Toestand
END AND
c.Goedkeuringaanvragen = CASE
WHEN @goedkeuringAanvragen <> -1 THEN @goedkeuringAanvragen
ELSE c.Goedkeuringaanvragen
END
ORDER BY
c.BestellingItemid DESC
This says if I have a valid value (not -1) compare to the parameter else match the column value (return all rows). You don't use a second WHERE to add criteria you use AND.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply