I need to modify a stored procedure in a production environment. I first drop the existing procedure and add the modified one back in with the same name. I then make a call to sp_recompile with the new procedure name.

If I look in Enterprise Manager, the new code is in place, but when I execute it from my app, the original code runs unless I add "with recompile" to my call.

I assume I'm missing something simple to get the server to use the new stored procedure. I really can't ask our users to stop and restart the SQL server every time we need to modify a stored procedure. Anyone know what I'm doing wrong?

Thanks,

Rick