June 29, 2006 at 4:29 am
Dear all
I have the following code in a stored procedure:-
@priceMin int,
@priceMax int
AS
BEGIN
IF @priceMin = '0' SET @priceMin = NULL
IF @priceMax = '0' SET @priceMax = NULL
Declare @sql varchar(1000)
-- Selection of the column names
SET @sql = 'SELECT Cars.carID, Cars.fk_vehicleClassID, Cars.carRegNo, Cars.fk_MakeID, Cars.fk_ModelID, Cars.carPrice, Cars.carYear,
Cars.carMileage, Cars.fk_EngineSizeID, Cars.carKms, Cars.fk_GearboxID, Cars.fk_FuelID, Cars.fk_BranchID, Cars.carDesc,
Cars.carSPOffer, Cars.carSPOfferText, Make.makeTit, Model.modelTit, Cars.fk_VehicleStatusID
FROM Cars INNER JOIN Make ON Cars.fk_MakeID = Make.makeID INNER JOIN Model ON Cars.fk_ModelID = Model.modelID
WHERE Cars.fk_VehicleStatusID = 1'
-- Price Range Calc
IF @priceMin IS NOT NULL OR @priceMax IS NOT NULL
IF @priceMax = '999999'
BEGIN
SET @sql = @sql + ' AND carPrice > '+CAST(@priceMin AS varchar(4))
END
ELSE
BEGIN
SET @sql = @sql + ' AND carPrice BETWEEN '+CAST(@priceMin AS varchar(4)) +' AND '+CAST(@priceMax AS varchar(4))
END
This works fine when i insert 0 for priceMin and 0 for priceMax, however, when I insert a number, for example 10000, this stored proc breaks.
can you please help me out?
Thanks
Johann
June 29, 2006 at 4:57 am
Hi Johann,
The problem is because you are casting @priceMin and @priceMax as varchar(4) but the value 10000 is 5 characters long so you would need to cast it to varchar(5).
That said, an integer value could in theory be as big as 11 characters long so you should really cast integer values to varchar(11).
Hope that helps,
June 29, 2006 at 5:12 am
Hi Karl
I did as you said, varchar(11) however it did not return any results.
So the problem I think is a bit deeper!
June 29, 2006 at 5:28 am
Hi Johann,
I'm assuming that you're running sp_executesql @SQLCmd or exec(@SQLCmd) somewhere inside the proc.
Unfortunately only you can tell what's going wrong with the query as you have access to the data.
I would run the following statement once you have finished with setting the parameter: PRINT @SQLCmd
You can then see the exact SQL statement that is being run and try and determine why it isn't returning any results.
Hope that helps,
June 29, 2006 at 5:32 am
Infact, that is what I did
I did a Print @sql at the end, and when both are 0, it gives me the correct SQL statement, but when I insert a number, it just fails and no SQL is printed.
The data is normal int fields with numbers entered in them, so should not be a real problem with the data I think.
I am just confused since I cannot detect how its not writing any SQL code!
June 29, 2006 at 5:39 am
Ok,
I see what the problem is.
Put this statement at the top of your procedure:
set concat_null_yields_null off
When you cast @maxPrice (or @minPrice), if @maxPrice is null you'll end up with a null. And by default, when you concatenate two strings together, if one of them is null you'll end up with null. The set statement overrides that behaviour.
June 29, 2006 at 5:43 am
hmmm I think I am getting there
Now I am getting this:-
SELECT Cars.carID, Cars.fk_vehicleClassID, Cars.carRegNo, Cars.fk_MakeID, Cars.fk_ModelID, Cars.carPrice, Cars.carYear,
Cars.carMileage, Cars.fk_EngineSizeID, Cars.carKms, Cars.fk_GearboxID, Cars.fk_FuelID, Cars.fk_BranchID, Cars.carDesc,
Cars.carSPOffer, Cars.carSPOfferText, Make.makeTit, Model.modelTit, Cars.fk_VehicleStatusID
FROM Cars INNER JOIN Make ON Cars.fk_MakeID = Make.makeID INNER JOIN Model ON Cars.fk_ModelID = Model.modelID
WHERE Cars.fk_VehicleStatusID = 1 AND carPrice BETWEEN AND 1000 ORDER BY carPrice ASC
So the SQL does not run, since BETWEEN is not good
June 29, 2006 at 5:48 am
I guess you'll have to decide on a value for @minPrice and @maxPrice in the case that they are null.
something like:
isnull(CAST(@priceMin AS varchar(4)),0) --uses 0 if @priceMin is null
If 0 isn't suitable then you'll need to use another integer that works.
June 29, 2006 at 6:34 am
That's perfect!!
Thanks for your help Karl
Johann
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply