Thanks to all who provide the helpful tips.

Sergey gave me a good insight as why #TempCustNew is not available if created by parameterised SPT. This means I will have to use another approach, as suggested by Kenneth's first alternative, although I prefer parameterised SPT due to the advantages suggested by Chuck Urwiler in his book "Client-Server Applications w/h Visual FoxPro and SQL Serve".

What I actually want is to have a function that creates a medium size temporary table (~10K records) from a large table (~5M records). Then have other functions that read from this temporary table, before returning the results back to client. I do not want to return this temporary table to client because it is still a fairly big table. The temporary table is there mainly for speed purpose. I wonder if this is the right approach, or there are better ways doing it. Someone suggested using parameterised SQL-View but my understanding is that everytime when I access the SQL-View I am actually running the underlying query that creates the view. So speed is still the problem, isn't it?

I am fairly new to SQLServer. Someone told I should keep Stored Procedures to a minimum and never write Business Rules in Stored Procedures. Reasons being it is (a) Database dependant (code will not be portable if for example we change from SQLServer to Orcale); (b) The logic of the business rules not centralised; (c) a lot more troubles to release to clients. What do you all think?

Thanks
James