Global Temporary Stored Procedures

Global temporary stored procedures are related to temporary stored procedures as global temporary tables are related to private temporary tables. They also reside in the tempdb database, but they use the prefix ##. You create them in the same way you create temporary stored procedures. The only difference is that they are visible and usable from all sessions. In fact, permissions are not required and the owner cannot even deny other users access to them.

When the session that has created the procedure is closed, no new sessions will be able to execute the stored procedure. After all instances of the stored procedure already running are finished, the procedure is dropped automatically.

The following code example creates a global temporary stored procedure:

      Create Procedure ##apEquipment_Insert           @Make varchar(50),           @Model varchar(50),           @EqType varchar(50)      as           declare @EqTypeId smallint           select @EqTypeId = EqTypeId  -- This is OK in a perfect world,           from dbo.EqType              -- but it is based on the           Where EqType = @EqType       -- unreasonable assumption that                                        -- you can identify the key using                                        -- the description.           Insert dbo.Equipment (Make, Model, EqTypeld)           Values (@Make, @Model, @EqTypeld}