How I can optimize this code can run in O(n) for assign value in @TollPrice :
IF (EXISTS (SELECT TollPrice
FROM Car_TaxInfo
WHERE (car_subgrp_id = @Kind) AND (Sal = @Sal)))
BEGIN
SELECT @TollPrice = TollPrice
FROM Car_TaxInfo
WHERE (car_subgrp_id = @Kind) AND (Sal = @Sal)
SET @IsExistToll = 1
END
ELSE
BEGIN
SET @IsExistToll = 0
END
You don't need to verify for existence here:
SET @TollPrice = NULL --this is mandatory. If @TollPrice contains some value then it will retain that value after below statement if there will be no matching rows.
SELECT @TollPrice = TollPrice
FROM Car_TaxInfo
WHERE (car_subgrp_id = @Kind) AND (Sal = @Sal)
IF @TollPrice IS NOT NULL
SET @IsExistToll = 1
ELSE
SET @IsExistToll = 0
If TollPrice can be NULL itself then you can use @@ROWCOUNT
SELECT @TollPrice = TollPrice
FROM Car_TaxInfo
WHERE (car_subgrp_id = @Kind) AND (Sal = @Sal)
IF @@ROWCOUNT > 0
SET @IsExistToll = 1
ELSE
SET @IsExistToll = 0
Even more, you can do the following:
SET @IsExistToll = 0
SELECT @TollPrice = TollPrice, @IsExistToll = 1
FROM Car_TaxInfo
WHERE (car_subgrp_id = @Kind) AND (Sal = @Sal)
Is your system a OLAP system or PLTP one? if it is OLAP or it is OLTP with acceptable amount of INSERT/UPDATE/Delete you may want to add index for car_subgrp_id and Sal column. In IF select clause you may combine your query with TOP 1 and replace EXISTS with this new query. I mean your new query may looks like this:
Declare @tPrice INT
IF (SELECT TOP 1 @tPrice=TollPrice
FROM Car_TaxInfo
WHERE (car_subgrp_id = @Kind) AND (Sal = @Sal) IS NOT NULL)
SET @IsExistToll = 1
ELSE
SET @IsExistToll = 0
