>>>>Hi
>>>>I have a SP which accept parameter of "EmployeeNoList". The value of the parameter could be '00001, 00002, 00100, 00120'. The # of employeeNo in the list could be few to hundred/thousand.
>>>>
>>>>Currently, I use a function that will split the list into table and INNER JOIN with "actual" complex SQL statement in order to speed up the performance / prevent use of dynamic SQL. I was told that is some way to have better performance. Anyone shall share how to optimize it?
>>>>
>>>>Thank you
>>>
>>>I would not pass a list of employee numbers as parameters. Consider refactoring the app to pass one employee number at a time and retrieve matching records for that employee. That will be fully optimizable. For sure you do not want to pass the SP a few hundred/thousand parameters. Keep it clean and simple.
>>
>>I don't think that's very good advice. If your list contains (like some of ours that we pass as parameters) upwards of 100's of keys, you would be performing 100's of data calls given your methodology. Ours makes one all and returns the data for all the keys passed. SQL Server has functions that handle chopping lists like that, makes it very easy...
>
>Reasonable people can disagree. We have certainly disagreed more profoundly than this.

Very few people are reasonable.