SQLTeam.com | Weblogs | Forums

Legacy headache -- looking for a best solution


I have an issue where I have alot of legacy sql queries with a where clause that is of the form

where systemname in (select systemname from tblsystem)

I now have a directive to expand this to allow for other columns to be added to the where clause, so column names can be dependent on the select statement

something along the lines of

where @columnname in (select columnname from tblsystemfilter)

I'll know the column name and value from the select. Im trying to figure out the best way to move it into the other stored procs where clauses.

there are only 4 new columns where the information can come from and there are alot of procedures to modify. If possible I would like to avoid dynamic sql.

any suggestions on how to approach this?


Since you have only four choices, you could do something like shown below (where I am showing only two, but easily expandable to four).

	col1, col2, col3
	YourMainTable a
	INNER JOIN tblSystemFilter  f ON
		( @columnName = 'systemname' AND f.systemName = a.SystemName )
		( @columnName = 'username' AND f.username = a.username )

However, it is quite possible that you will run into performance problems because of parameter sniffing. To avoid that you would need to use dynamic SQL. Perhaps you have already investigated the dynamic SQL option, and if you have not reviewed these articles, please do:

Gail Shaw
Erland Sommarskog

If you do use inner join, depending on the nature of the data in tblSystemFilter, you might end up getting multiple rows where only one should be returned. If that is the case, you would need to refine your query by selecting only distinct entries from tblSystemFilter and joining to that virtual table.

Also, the behavior can be different when you use inner join or exists clause instead of using the IN construct as you are doing if there are null values in the tblSystemFilter table. If you were depending on that behavior, you would want to test to make sure that the new behavior is acceptable.


Do you think it might be worth using UNION ALL, rather than the OR of the INNER JOIN?


thanks for your answers -- i went with the union all.