error in stored proc - 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)

error in stored proc

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2006-06-29 : 06:28:57

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-06-29 : 06:45:05

Can you be little specific and tell us exactly what happens when you punch in any non-zero value ?

I think the problem is with the cast statement because if you punch 10000 to cast into varchar(4), you are going to get crazy output. Increase the size say varchar(10) and check the result.

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources