Actually found that the problem was because I was compiling the proc with ANSI_NULLS OFF, the minute I changed it back to ON the recompiling stopped once the plan was available in cache.
Now that that part of the problem was taken care of I started finding some other peculiar behavior. When I run the proc from Query Analyzer from different client PCs it uses the cached plan. The minute I run the same proc from the Internet application for which it was designed, it seems to recompile again and again from the different client PCs if you run the app it uses the cache plan. Going back to Query Analyzer again flushes the cache.
It appears like running the proc from two different apps results in flushing the cache. Have you seen this kind of behavior and what is the reason for it? Is there a way around it at all?
Thanks for any help,