September 5, 2006 at 10:27 am
This is my code:
create PROCEDURE GetPriceRange
(@Range varchar(4))
AS
Select p.ProductID, p.Model, p.[Name], p.[Description], p.Price,
p.OnDepartmentDisplay, p.OnCatalogDisplay,
p.BrowseDisplay, p.SupplierID, p.FinishID, p.BladeSpan,
p.Price - (Price * 0.20) "OurPrice",
p.Price - (Price * 0.25) "SalePrice",
p.BladeFinish1, p.BladeFinish2,
p.Pic
From Product
Having p.Price between
case
when @Range = '100' Then
'100 and 199'
when @Range = '300' Then
'200 and 399'
when @Range = '500' Then
'400 and 599'
when @Range = '700' Then
'600 and 799'
when @Range = '900' Then
'800 and 999'
when @Range = '1000' Then
'1000 and 5000'
end
The above code creates the following error:
"Incorrect syntax near 'end'"
My syntax all seems fine to me.
PLEASE HELP!! What am I doing wrong?
September 5, 2006 at 10:31 am
Please tell us what you want the select statement to do. Do you want to display range? If so then it has to be in Select Clause not in "Having". Having is used to summary (after Group BY etc).
Hope this helps
Sreejith
September 5, 2006 at 10:35 am
I am sending a variable with a value of either 100, 300, 500, 700, 900 or 1000 to the stored procedure
if the variable @Range = 100 then I want it to return rows with a price that is between 100 and 199
if its 300 then return rows between 200 and 399
500 then 400 and 599
and so on...
September 5, 2006 at 10:50 am
having is in there by mistake
having = where
September 5, 2006 at 10:55 am
Try this:
create PROCEDURE GetPriceRange
(@Range varchar(4))
as
Begin
Select @PriceFrom = Case when @Range = '100' Then 100
when @Range = '300' Then 200
when @Range = '500' Then 400
when @Range = '700' Then 600
when @Range = '900' Then 800
when @Range = '1000' Then '1000' Else 0 End,
Select @PriceTo = Case when @Range = '100' Then 199
when @Range = '300' Then 399
when @Range = '500' Then 599
when @Range = '700' Then 799
when @Range = '900' Then 999
when @Range = '1000' Then '5000' Else 5000 End
Select p.ProductID, p.Model, p.[Name], p.[Description], p.Price,
p.OnDepartmentDisplay, p.OnCatalogDisplay,
p.BrowseDisplay, p.SupplierID, p.FinishID, p.BladeSpan,
p.Price - (Price * 0.20) "OurPrice",
p.Price - (Price * 0.25) "SalePrice",
p.BladeFinish1, p.BladeFinish2,
p.Pic
From Product
where p.Price between @PriceFrom and @PriceTo
End
September 5, 2006 at 10:56 am
I Forgot to add Declare part.
create PROCEDURE GetPriceRange
(@Range varchar(4))
as
Begin
Declare @PriceFrom decimal(10,2),
@PriceTo decimal(10,2)
Select @PriceFrom = Case when @Range = '100' Then 100
when @Range = '300' Then 200
when @Range = '500' Then 400
when @Range = '700' Then 600
when @Range = '900' Then 800
when @Range = '1000' Then '1000' Else 0 End,
Select @PriceTo = Case when @Range = '100' Then 199
when @Range = '300' Then 399
when @Range = '500' Then 599
when @Range = '700' Then 799
when @Range = '900' Then 999
when @Range = '1000' Then '5000' Else 5000 End
Select p.ProductID, p.Model, p.[Name], p.[Description], p.Price,
p.OnDepartmentDisplay, p.OnCatalogDisplay,
p.BrowseDisplay, p.SupplierID, p.FinishID, p.BladeSpan,
p.Price - (Price * 0.20) "OurPrice",
p.Price - (Price * 0.25) "SalePrice",
p.BladeFinish1, p.BladeFinish2,
p.Pic
From Product
where p.Price between @PriceFrom and @PriceTo
End
September 5, 2006 at 11:01 am
The code you provided results in the following error:
Incorrect syntax near the keyword 'Select'.
Refering to the second select statement
September 5, 2006 at 11:08 am
I removed the coma and it works fine
thanx alot your a genius
September 5, 2006 at 11:08 am
This should work.
create PROCEDURE GetPriceRange
(@Range varchar(4))
as
Begin
Declare @PriceFrom decimal(10,2),
@PriceTo decimal(10,2)
Select @PriceFrom = Case when @Range = '100' Then 100
when @Range = '300' Then 200
when @Range = '500' Then 400
when @Range = '700' Then 600
when @Range = '900' Then 800
when @Range = '1000' Then '1000' Else 0 End,
@PriceTo = Case when @Range = '100' Then 199
when @Range = '300' Then 399
when @Range = '500' Then 599
when @Range = '700' Then 799
when @Range = '900' Then 999
when @Range = '1000' Then '5000' Else 5000 End
Select p.ProductID, p.Model, p.[Name], p.[Description], p.Price,
p.OnDepartmentDisplay, p.OnCatalogDisplay,
p.BrowseDisplay, p.SupplierID, p.FinishID, p.BladeSpan,
p.Price - (Price * 0.20) "OurPrice",
p.Price - (Price * 0.25) "SalePrice",
p.BladeFinish1, p.BladeFinish2,
p.Pic
From Product
where p.Price between @PriceFrom and @PriceTo
End
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply