SQL Server – Do temporary work with temporary Stored Procedures & tables

SQL Server temporary tables and global temporary tables:

For temporary work, we use temporary tables and global temporary tables. There is nothing complex about their usage. Use Temp tables if only your sessions needs access to the data in the temp table using the #TempTable naming format. To share data in a temp table across sessions in use global temp tables using the ##GlobalTempTable format.

Example of a Global temp table

CREATE TABLE ##TempAttributesTable
(
[ConnectionId] UNIQUEIDENTIFIER NOT NULL,
[Name] VARCHAR(100) NOT NULL,
[Value] SQL_VARIANT,
[Type] VARCHAR(25),
[Format] VARCHAR(100),
[At] DATETIME2 NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Once created, this table could be accessed from another connection/session (that did not create this table). Local temp tables get dropped when they are dropped explicitly or when the creator session ends. Similarly, the global temp tables get dropped when the creating session ends and if there are no references to the table from other sessions or when the last referencing session ends.

Temporary Stored procedures? Yes.

Now, what is not common is that you could also temporary stored procedures that exhibit similar characteristics as the temp tables we just saw. Again you can create local or global temp stored procedures.

IF object_id('TEMPDB.DBO.#MainProc') IS NOT NULL
DROP PROCEDURE #MainProc;
GO

The above code shows how to drop an existing temp procedure and below, you can see how to create a temp stored procedure.

CREATE PROCEDURE #MainProc
AS
BEGIN

-------------------------------
-- DO SOMETHING THAT YOU NEED TO
-------------------------------
DECLARE @AppContextInfo VARBINARY(128) = CAST('My Test Application' AS VARBINARY(128))

--This information will be associated with the session and will be accessible in SQL Server system views
SET CONTEXT_INFO @AppContextInfo

EXEC Logging.SetAttribute
    @AttributeName='Log.ParentObjectId',
    @AttributeValue=@CallerProcId,
    @AttributeType='BIGINT',
    @AttributeFormat=NULL;

END

The usage of these stored procedures is exactly like regular procedures.

Exec #MainProc

Conclusion

The real benefit of these procedures is when they contain lot of logic that you need on a temporary basis but do not want to clutter the existing stored procedure list. You could even have multiple temporary procedures that call each other. I would not go overboard with this. It is just a convenience.