Update (JohnG): It appears that the posting (excerpt in the quoted text below) that I reference in my reply has been removed by the original poster after reading my response.
where (charindex(d.clnt_no,@client, 1)>0)
For starters, because you are using a function on the search column (clnt_no) in the WHERE clause, you will be negating any index usage on this column. Try this with a large table and see the query plan.
Additionally, depending upon the list of values that are passed in the parameter in conjunction with the values that are in the table, you could get a lot of "false positives" due to the character matching behavior of CHARINDEX. E.g., given clint_no values of 100 and 1000, a WHERE clause of: CHARINDEX(CAST(d.clnt_no AS VARCHAR), '1000', 1) > 0 will incorrectly
return both 100 and 1000!
(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.