>Well, I can't offer possible solutions to your problem, but I can relate my own experiences with stored procedures on MS SQL Server 6.0. In my case, I've never really noticed that SPs take longer to execute than ISQL/W batch commands (well, now that you mention it, I think they do).
>
>However, sometimes for me, a relatively complicated piece of code will execute fine when run as ISQL/W batch, but fails terribly as an SP. That is, the code runs fine without errors, but the data (it's supposed to perform regular updates on a table using data from other tables using cursors and local variables as it goes) that's written has all NULLs. When run as ISQL/W batch, everything's fine! I finally worked around this by redesigning the whole business rules/stored procedure thing.

I think it is a resource thing. SQL will compile transact SQL statements before it runs them, whether it is in an SP or ISQLW. A block of code in ISQLW will take up less resources than a whole SP. I also bumped up the allocated memory (the moron before me had only 16 megs allocated to SQL Server) and procedure cache. It made my users more happy, but not my SP. So, I hacked out some unoptimized code (a select statement that searches for duplicates using the IN clause) and it speed up a little. It now takes an hour to run what takes 30 minutes in ISQL. Better, but not great.

>Like you, man, I'm totally lost on this.
I can see why you are "distressed"! (s)

If you solve your problem, let me know. As my luck is going, I'll run into to it as well!

And remember, as Dan Rather used to say, "Courage!"

Thanks,
Scot.