Wednesday, 14 August 2013

SQL: UNION ALL tables if @@rowcount from first SELECT yields less than 10 rows

SQL: UNION ALL tables if @@rowcount from first SELECT yields less than 10
rows

I'm trying to do a stored procedure to be used by a search mechanism.
The way I want it to work is to first do a SELECT with LIKE TOYOTA%. After
that, if the results yield less than 10 results, I want it to append or
UNION ALL with another SELECT that has a LIKE %TOYOTA%.
So Basically, this is what I'm looking for:
SELECT *
FROM CARS
WHERE CARS.MAKE LIKE '@searchQuery%'
IF(@@rowcount < 10)
BEGIN
UNION ALL
SELECT *
FROM CARS
WHERE CARS.MAKE LIKE '%@searchQuery%'
END
The only problem is that I can't do this - it won't let me use UNION ALL
before or after the IF.
I'm doing this because I want to always have at least 10 results as much
as possible. If I have less, then I want to fill the remaining slots with
records that may have the name TOYOTA somewhere in the middle.
Thanks!!!

No comments:

Post a Comment