How to create a temporary Stored Procedure using TempDB

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

Option 1 – Stored Procedure available only for 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.

 

Option 2 – Stored Procedure available to more than your session

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.

Read More on Stored Procedures

SQL Server - Grant execute on all stored procedures - SQL Server ...

SQL Server - Last time a stored procedure was executed - SQL ...

Xp_cmdshell – the most dangerous extended stored procedure


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to create a temporary Stored Procedure using TempDB


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer