>>>>>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.
>
>Ducking issues again? Seriously, how can you justify 100's of round trips to the data base to "keep it clean and simple" vs 1 single call to get all data?

Seriously, I do not call a round trip to the server a big hit. That's just the way it's done. My point was based on software maintenance, which is way more expensive than server hits.