Few people know that it is possible to create temporary stored procedures in SQL Server. I often ask about this when recruiting new candidates, and 9 out of 10 ask if I meant temporary tables, or if they start talking about them right away. So yes, we can indeed create temporary stored procedures in SQL Server, but we should do so with a good understanding of their behavior and follow Microsoft’s recommendations.
Temporary stored procedures are created in the same way as temporary tables – by adding one or two # before their name. Based on this, they are distinguished as local and global, similar to temporary tables:
- local (#)
- created by adding one hashtag (#) before their name
- visible only in the connection (SPID) in which they were created
- automatically removed after the connection is closed
- can only be called by their creator – the owner (logically, the login that owns the active connection)
- cannot grant the right to call them to other users
- global (##)
- created by adding two hashtags (##) before their name
- visible to all connections (SPIDs) of the given instance
- automatically removed after the last connection using them is closed
- can be called by all users and this right cannot be revoked
Temporary stored procedures can be created by all users and this right cannot be revoked.
An example of a simple local stored procedure might look like this:
CREATE PROCEDURE #PrintMessageUpperCase (
@Msg NVARCHAR(100)
)
AS
BEGIN
PRINT UPPER(@Msg)
END
GO
EXECUTE [dbo].[#PrintMessageUpperCase] @Msg = N'Test Message'
GO
DROP PROCEDURE [dbo].[#PrintMessageUpperCase]
GO
The use of temporary stored procedures is really useful in various deployment scenarios of database changes, where we need to run more complex scripts or achieve the desired behavior of SQL Server by wrapping the code in a stored procedure. Similarly, they are excellent for testing various optimizations, etc. In the end, we do not need to worry about cleaning the database of temporary objects.
What we really need to be careful about is security: EXECUTE rights cannot be restricted for global temporary stored procedures, so code that a user would not normally have access to can be run from another session.