select top @variable
-
I'm trying to get the topcount as a varaible to pass the SP.
How would I go about doing that?? right now I get the message of incorrect syntax.
Thanks
CREATE PROCEDURE uspGetConquestCarstest
@vehicleNumber INT,
@priceRangeFactor FLOAT = .25,
@topnum int
AS
BEGIN
SET NOCOUNT ON
set @topnum = 6
IF @vehicleNumber > 0
DECLARE
@brandName VARCHAR(28),
@modelName VARCHAR(30),
@bodyStyle VARCHAR(30),
@vehicleClass VARCHAR(50),
@msrp DECIMAL,
@lowEndPriceRange DECIMAL,
@highEndPriceRange DECIMAL
SELECT
@brandName = b.brand_name,
@modelName = v.model_name,
@bodyStyle = v.bodystyle,
@vehicleClass = v.vehicle_class,
@msrp = v.base_list_price,
@lowEndPriceRange = @msrp - (.25 * @msrp),
@highEndPriceRange = @msrp + (.25 * @msrp)
FROM
Vehicle v
LEFT JOIN
Brand b
ON b.brand_nmb = v.brand_nmb
WHERE
v.vehicle_nmb = @vehicleNumber
IF @@ROWCOUNT > 0
SELECT TOP @topnum
conquestBrand.brand_name,
conquestVehicle.brand_nmb,
conquestVehicle.model_name,
conquestVehicle.trim_name,
conquestVehicle.year,
conquestVehicle.vehicle_nmb,
image.evox_id,
conquestVehicle.base_list_price,
conquestVehicle.base_invoice_price
FROM
Vehicle conquestVehicle
LEFT JOIN
Brand conquestBrand
ON conquestBrand.brand_nmb = conquestVehicle.brand_nmb
LEFT JOIN
Nimages image
ON image.vehicle_nmb = conquestVehicle.vehicle_nmb
WHERE
conquestVehicle.vehicle_class = @vehicleClass
AND conquestVehicle.bodystyle = @bodyStyle
AND conquestVehicle.base_list_price BETWEEN @lowEndPriceRange AND @highEndPriceRange
AND conquestBrand.brand_name <> @brandName
AND conquestVehicle.model_name <> @modelName
FOR XML AUTO
END
GO
-
set rowcount @topnum
select...
set rowcount 0 --reset to unlimited
-
SET @STR = 'SELECT TOP '+@count+' ...'
EXEC(@str)
-
There's no need to resort to dynamic sql for this problem... but it might perform better (if all validations are done correctly)!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply