if statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

if statement

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2006-04-27 : 05:59:49

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-27 : 06:07:04

This may help you
http://www.sqlteam.com/item.asp?ItemID=2077

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-27 : 06:08:25

"These 4 parameters can either contain data or be left blank in the code"
normally i don't use blank. I would use NULL.


select ...
from   ...
where  Tours.tourDestination = coalesce(@category, Tours.tourDestination)
and    . . .

And would call the SP this way. Passing NULL to the variables
exec stpGetToursFromToday '2006-01-01', 10, NULL, NULL, NULL, NULL

monfu
Yak Posting Veteran

81 Posts

Posted - 2006-04-27 : 06:22:03

I tried as you told me:-

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 AND
Tours.tourDestination = COALESCE(@destination, Tours.tourDestination) AND
tourDetailsCat.tourDetCatName = COALESCE(@category, tourDetailsCat.tourDetCatName) AND
month(Tours.tourDate) = COALESCE(@month, month(Tours.tourDate)) AND
year(Tours.tourDate) = COALESCE(@year, year(Tours.tourDate))

ORDER BY tourDate ASC
END
GO

However when i ran it through the Query Analizer, it gave me the following error:-

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(0 row(s) affected)

Stored Procedure: TravelDepartment.dbo.stpGetToursFromToday
Return Code = 0

can you help me out?

Thanks

Johann

monfu
Yak Posting Veteran

81 Posts

Posted - 2006-04-27 : 07:04:58

I am noticing that the problem is not the stored proc but the code, since in the SQL profile, i get this string:-

exec stpGetToursFromToday @statId = 1, @todaysdate = 'Apr 27 2006 12:00AM', @destination = default, @category = default, @month = 0, @year = 2006

So i need to pass @destination and @category null values. Is that correct?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-27 : 08:45:58

[code]CREATE PROCEDURE dbo.stpGetToursFromToday
(
@todaysDate datetime,
@statID int,
@destination varchar(50) = NULL,
@category varchar(50) = NULL,
@month int = NULL,
@year int = NULL
)[/code]

And call your sp this way. Pass in the date in YYYYMMDD format or YYYY-MM-DD
[code]exec stpGetToursFromToday @statId = 1, @todaysdate = '20060427'[/code]

monfu
Yak Posting Veteran

81 Posts

Posted - 2006-04-27 : 08:55:31

Hi khtan

the variables will not always be NULL, so I cannot set them in the stored proc, only in code.

I am managing to set destination and categories to null from code, but month and year are giving me problems.

I converted month and year to string values too, and when they are blank I am setting them to strMonth = "" and strYear= "", but the stored proc is reading them as '' instead of null

My problem is here:-

When the user does not choose anything, I want to pass NULL to the stored Proc to return all the results.

So I am setting these :-

strDest = "";
strCat = "";
strMonth = "";
strYear = "";

My problem is that strDest and strCat are getting set to null correctly, however, strMonth and strYear are remaining '' and as so the stored proc does not work

Here is the stored proc:-

CREATE PROCEDURE dbo.stpGetToursFromToday
(
@todaysDate datetime,
@statID int,
@destination varchar(50),
@category varchar(50),
@month varchar(50),
@year varchar(50)
)
AS
BEGIN

SELECT Tours.tourID, Tours.tourDate, Tours.tourStatus, Tours.tourDestination, Tours.tourDuration,

Tours.tourPrice, tourDetailsCat.tourDetCatName, MONTH(Tours.tourDate) AS Month, YEAR(Tours.tourDate) AS Year
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 = COALESCE(@destination, Tours.tourDestination) AND
tourDetailsCat.tourDetCatName = COALESCE(@category, tourDetailsCat.tourDetCatName) AND
month(Tours.tourDate) = COALESCE(@month, month(Tours.tourDate)) AND
year(Tours.tourDate) = COALESCE(@year, year(Tours.tourDate))

ORDER BY tourDate ASC
END
GO

What can i do?

monfu
Yak Posting Veteran

81 Posts

Posted - 2006-04-27 : 09:59:17

I tried this in code:-

if (ddlCat.SelectedValue != "No Categories")
{
strCat = ddlCat.SelectedValue;
}
else
{
strCat = "-1";
}

and for all other values that can be blank, and in the stored proc, I am doing this:-

CREATE PROCEDURE dbo.stpGetToursFromToday
(
@todaysDate datetime,
@statID int,
@destination varchar(50),
@category varchar(50),
@month varchar(50),
@year varchar(50)
)
AS
BEGIN

IF @destination = "-1" SET @destination = NULL
IF @category = "-1" SET @category = NULL
IF @month = "-1" SET @month = NULL
IF @year = "-1" SET @year = NULL

SELECT Tours.tourID, Tours.tourDate, Tours.tourStatus, Tours.tourDestination, Tours.tourDuration,

Tours.tourPrice, tourDetailsCat.tourDetCatName, MONTH(Tours.tourDate) AS Month, YEAR(Tours.tourDate)

AS Year
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 = COALESCE(@destination, Tours.tourDestination) AND
tourDetailsCat.tourDetCatName = COALESCE(@category, tourDetailsCat.tourDetCatName) AND
month(Tours.tourDate) = COALESCE(@month, month(Tours.tourDate)) AND
year(Tours.tourDate) = COALESCE(@year, year(Tours.tourDate))

ORDER BY tourDate ASC
END
GO

However it is giving me an error:-

invalid coloumn name '-1' for all of the if statements

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-27 : 10:04:21

Instead of double quotes, use single quote
Also why did you use varchar datatype?
Use int datatype and check
IF @destination = -1 SET @destination = NULL

Madhivanan

Failing to plan is Planning to fail

monfu
Yak Posting Veteran

81 Posts

Posted - 2006-04-27 : 10:16:08

Thanks to all of you

seems to be working now

Thanks again

Johann

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources