I went with your suggestion. I was worried that it would recompile the SP all the time and slow down (not that I really know anything about SPs and compilation), but it seems to be still very quick and does the job fine
What type of variable is @Var? Is it a string representation like '1, 2, 3' or is it a single integer value, or a table variable? I've gotten away with sql code like this before without using dynamic sql but running statements like:
select * from tblX where (fldX in (@var)) or (fldX is null)
...but whether this works depends on what @Var is. The IN statement is not all that versatile about accepting parameters.
I would drop IN and dynamic SQL like a rock. There is a much more elegant way of doing it, and it works no matter whether @var has values or not.
- Create a temp table with one field of the required data type
- Parse your @var and store all its values into this temp table
- Either JOIN with this table blindly (NPI), or JOIN only if temp table contains at least one row.
I am using a db of several tens of thousands of people submitting these searches every few seconds. Surely there would be some impact on constantly inserting and deleting out of temporary tables.
@var in the original post was a varchar such as '1,2,4,7'. There are in actual fact 10 different @var's being passed, hence I either had to read in all the values, which meant reading select x_key from 10 tables and concatonating with a comma (slow) or hard coding (no).
There is no need to delete. Besides, if you use @table instead of #table, you woiuldn't even generate an IO (unless we are talking about 10,000 values which may cause paging). Recently I rewrote a search routine used here by 40K internal and over 3 million external users using the concept that I described in my previous posting. The search is performed against 40K+ records in 7 tables. Max delay is under a second. I am not so sure about your "Surely"