I need help to optmize the SQL logic in one of my functions. Please, note that I am not able to use store procedure.
Here is my table. It will be initialized using @MainTable that contains a lot of records.
DECLARE TABLE @ResultTable
(
ResultValue INT
)
These are tables that stores some parameters - they can be emty too.
DECLARE TABLE @ParameterOne (ParameterOne INT)
DECLARE TABLE @ParameterTwo (ParameterOne NVARCHAR(100))
...
DECLARE TABLE @ParameterN(ParameterN TINYINT)
Now, I need to join a lot of tables to my @MainTable in order to select from it only some of its records. The selected records depend on the information stored in the parameters table.
So, my current solution is:
INSERT INTO ResultTable(ResultValue)
SELECT ResultValue
FROM MainTable M
INNER JOIN @MainOne MO
ON M.ID=MO.ID
....
INNER JOIN @MainN MN
ON M.IDN=MN.ID
WHERE (EXISTS (SELECT 1 FROM @ParameterOne WHERE ParameterOne=MO.ID) OR NOT EXISTS (SELECT 1 FROM @ParameterOne))
AND
...
AND
(EXISTS (SELECT 1 FROM @ParameterN WHERE ParameterN=MN.Name) OR NOT EXISTS (SELECT 1 FROM @ParameterN ))
So, the idea is to add the records only if they match the current criteria from the parameters tables.
Because I am not able to use procedure to build dynamic query I am using the WHERE clause with combinations of EXISTS and NOT EXISTS for each parameter table.
The problem is that it works slower when I am adding more and more parameters table. Is there an other way to do this without using a lot of IF/ELSE statements checking what parameter table has records - it will make the function a lot bigger and difficult for read.
And ideas and advices are welcomed.