question

I originally had a sproc that used a view to bring back some of it's data as the sproc has a where clause on a concatenated field and at the time I felt the most efficient way of doing this was creating the concatenated field in the view and joining that to original tables and using the concatenated field in the view for the where clause. I then had to add more tables into my view and the time it took became unusable. At the starting point it was 9 sec and I needed to get it to c1 sec. I used the execution plan, created indexes and got it down to 4 seconds. I took the view out and in the sproc I put the data I needed in a temp table putting the where clause in the insert statement of the table, thus creating a table with much fewer rows. This got the execution time down to 2 secs which I was OK with. However, I have now found that although the sproc works perfectly in SQL our app doesn't like it so need to take the temp table out. I created a table outside the sproc and just populated it in the sproc and the app still does not like it. Can anyone please advise me on what I can do to get the data I need out, and have an acceptable execution time. I am on SQL 2000 so can't use CTE unfortunately.