I have a large millions+ rows of record that in a table that I want to do an inner join when a parameter is provided, if not do a filter based search. Long term solution is to split up the logic, but I need some assistance in the short term.
I'm trying to do something like below:
params:
@movie_ids int_table (optional passing in a table of only ints here)
@filter_name
@filter_genre
@filter_actor_name
DECLARE @id_count = sets movie id count here or 0;
SELECT
m.Id
m.Name
m.ShortName
m.Genre
m.ImageUrl
FROM Movies m WITH(NOLOCK)
IF movie_ids <> 0
BEGIN
INNER JOIN @movie_ids mids
ON mids.id = m.Id
END
ELSE
LEFT OUTER JOIN code_genres cg
ON m.GenreId = cg.Id
INNER JOIN Actors a
ON a.name LIKE '%@filter_actor_name%'
END
WHERE m.Name LIKE '%filter_name%'
OR m.ShortName LIKE '%filter_name%'
Sorry I couldn't provide the actual stored procedure since I'm not the original writer. The problem in short term I'm trying to figure out is to find out if I can do this conditional joins.
Currently it has a LEFT OUTER JOIN with a where clause that suppose to do the conditional, but the amount of data it is returning before the filter occurs is too much - too slow. Something like here
Let me know if I can provide more details.
(Long terms solution is done - split up the logic from sql, but I need something for now)
Aucun commentaire:
Enregistrer un commentaire