April 27, 2006 at 4:05 am
Dear All,
I am not very experienced with SQL Server, so I have a problem. I am passing 4 parameters from code to a stored proc. These 4 parameters can either contain data or be left blank in the code. I am trying to construct an sql statement, depending if they contain data or not. So I tried to do something like this:-
CREATE PROCEDURE dbo.stpGetToursFromToday
(
@todaysDate datetime,
@statID int,
@destination varchar(50),
@category varchar(50),
@month int,
@year int
)
AS
BEGIN
SELECT Tours.tourID, Tours.tourDate, Tours.tourStatus, Tours.tourDestination, Tours.tourDuration, Tours.tourPrice, tourDetailsCat.tourDetCatName
FROM TourDetails
INNER JOIN Tours ON TourDetails.fk_tourID = Tours.tourID
INNER JOIN tourDetailsCat ON TourDetails.tourDetCat = tourDetailsCat.tourDetCatID
WHERE Tours.tourDate >= @todaysDate
AND Tours.tourStatus = @statID
IF @destination <> ''
BEGIN
AND Tours.tourDestination = @destination
END
IF @category <> ''
BEGIN
AND tourDetailsCat.tourDetCatName = @category
END
IF @month <> 0
BEGIN
AND month(Tours.tourDate) = @month
END
IF @year <> 0
BEGIN
AND year(Tours.tourDate) = @year
END
ORDER BY tourDate ASC
END
GO
Can you tell me what the problem is and if it is the right way to do this.
Thanks for your help
Johann
April 27, 2006 at 5:34 am
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65291
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 28, 2006 at 8:12 am
Something along these lines might work:
SELECT Tours.tourID
, Tours.tourDate
, Tours.tourStatus
, Tours.tourDestination
, Tours.tourDuration
, Tours.tourPrice
, tourDetailsCat.tourDetCatName
FROM TourDetails
INNER JOIN Tours ON TourDetails.fk_tourID = Tours.tourID
INNER JOIN tourDetailsCat ON TourDetails.tourDetCat = tourDetailsCat.tourDetCatID
WHERE Tours.tourDate >= @todaysDate
AND Tours.tourStatus = @statID
AND Tours.tourDestination = CASE
WHEN @destination <> '' THEN @destination
ELSE Tours.tourDestination
END
AND tourDetailsCat.tourDetCatName = CASE
WHEN @category <> '' THEN @category
ELSE tourDetailsCat.tourDetCatName
END
AND Month(Tours.tourDate) = CASE
WHEN @month <> 0 THEN @month
ELSE Month(Tours.tourDate)
END
AND Year(Tours.tourDate) = CASE
WHEN @year <> 0 THEN @year
ELSE Year(Tours.tourDate)
END
ORDER BY tourDate ASC
April 28, 2006 at 8:14 am
thanks mkeast, but I used the COALESCE instead.
Thanks for all your help!
Johann
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply