Mike's Database Place

MSSQL PROCEDURE

Databases

SQL Server

MSSQL Databases

Temporary Stored Procedures

Local temporary stored procedure

Global temporary stored procedure

TEMPDB stored procedure

MSSQL Built-In Procedures

Temporary Stored Procedures

Local temporary stored procedure

Available only in the current session and is dropped when the session is closed.

CREATE OR ALTER PROCEDURE #MyProc AS ...

Global temporary stored procedure

Available in all sessions and is dropped when the session of the user that created it is closed.

CREATE OR ALTER PROCEDURE ##MyProc AS ...

TEMPDB stored procedure

Available in all sessions and is only dropped when SQL Server is restarted (at which point TEMPDB will be recreated thus wiping out your procedure).

USE tempdb

GO

CREATE OR ALTER PROCEDURE dbo.MyProc AS ...

Bibliography

(1) https://www.sqlservercentral.com/articles/temporary-stored-procedureshttps://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sqlhttps://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedurehttps://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedurehttps://www.brentozar.com/archive/2022/07/video-fundamentals-of-stored-procedures-at-sqlbits/https://www.w3schools.com/SQL/sql_ref_create_procedure.asp

Page updated

Google Sites

Report abuse