Stored Procedure error- And?
-
Hello,
How are you today?
I have a crosstab report which allow users to search on Country and a date range, but I wish to check if the date has not been entered and if so modify my query:
I wrote the following stored procedure:
CREATE PROCEDURE TouristArrivalsCrosstab
@Country nvarchar (255) = NULL,
@IncludeCountryinCrosstab varchar (3) = NULL,
@ArrivalDateFrom DATETIME,
@ArrivalDateTo DATETIME,
@IncludeDateRangeinCrosstab varchar (3) = NULL
AS SELECT
a.Country,
COUNT(*) AS 'TouristCount'
FROM
country AS a INNER JOIN Book1 AS b ON a.Country = b.Country
where (1=1)
And (b.Country = @Country or @Country = 'ALL')
if (@ArrivalDateFrom) <> ''
And (b.Arrival_date >=@ArrivalDateFrom And b.Arrival_date <= @ArrivalDateTo)
GROUP BY a.Country
GO
However, I receive an "Incorrect Syntax near the keyword 'Group' error. But if I make the following modification:
where (1=1)
And (b.Country = @Country or @Country = 'ALL') and
if (@ArrivalDateFrom) <> ''
(b.Arrival_date >=@ArrivalDateFrom And b.Arrival_date <= @ArrivalDateTo)
Then I receive a "Incorrect syntax near the keyword 'if' as well as a "Incorrect syntax near 'b'
Based on these errors I am wondering if T-SQL is having an issue with recognising And as part of the SQL query. Your thoughts?
-
You will need to change the "if" to a "case" (cannot use if in a select):
and b.arrival_date >=
case
when @ArrivalDateFrom = ''
then b.arrival_date
else @ArrivalDateFrom
end
and b.arrival_date <=
case
when @ArrivalDateFrom = ''
then b.arrival_date
else @ArrivalDateTo
end
As an if stmt is not valid in a select, sql is breaking the query down.
The top part before the if will not run without the group by.
The if on it's own has no valid result part, the stmt after the if starts with an and,and will cause problem, and then you have the group by on it's own at the bottom.
Replace the if with the case, and it should run as one batch.
-
...Oh, and I'm very well thanks.
-
Hi,
Just to clarify my requirements, I allow the user to search on a country:
(b.Country = @Country or @Country = 'ALL')
but then I only want to concatenate:
and (b.Arrival_date >=@ArrivalDateFrom And b.Arrival_date <=
@ArrivalDateTo)
if they entered a date range:
Trying this:
case
when @ArrivalDateFrom <> ''
then and (b.Arrival_date >=@ArrivalDateFrom And b.Arrival_date <=
@ArrivalDateTo)
end
but I keep getting "Incorrect syntax near the keyword 'case'"
-
No need for any of that. Just change the procedure to accept a NULL ArrivalDateFrom and ArrivalDateTo parameters and then do a where clause:
WHERE b.Arrival_Date
BETWEEN ISNULL(@ArrivalDateFrom, b.Arrival_Date)
AND ISNULL(@ArrivalDateTo, b.Arrival_Date) -
Hello,
Thanks a lot, that worked.
Tell me, is there a reference that one can use to learn these techniques, and which will show sample code. I have been using a reference at MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_tsqlcon_6lyk.asp
which is good but for example it doesn't speak to "if stmt is not valid in a select" which DavidT mentioned.
-
I think this forum's a pretty good place to go!
but for syntax type references, Books OnLine, which comes with SQL Server client tools, is the first place I always start.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply