29 September,2015 by Jack Vamvas
Question: I have to execute a stored procedure for troubleshooting – but permanent stored procedures are disallowed. Is there a way to create a temporary stored procedure - ?
Answer: Yes, you can create a temporary stored procedure in SQL Server , using tempdb. There are two options. A stored procedure available only to your session and a stored procedure available to more than your session
CREATE PROC #myStoredProcedure
If another session attempts to execute the stored procedure , the user will see a “cannot find the stored procedure” message.
CREATE PROC ##ourStoredProcedure
Restrictions on compiling permanent stored procedures can be a problem – but often there a good security reasons . Speak to the DBA about other workarounds.