You are probably familiar with table variables, local temp tables, and global temp tables but what some people don’t think about is that stored procedures and functions can also utilize the tempdb. As you might imagine, it can be difficult for you to think of a use for temporary stored procedures. It is almost always better to create a permanent stored procedure, but if you are a person that often has to look into servers that you can’t change (or maybe you simply don’t want to deploy a stored procedure that might only be used once and discarded) then a temporary stored procedure might be the best solution in your environment.
The technique is quite simple:
SET NOCOUNT ON GO CREATE PROC #MyTempStoredProc @UserID integer AS INSERT INTO MyTable (UserID) VALUES (@UserId) GO
You might want to use this technique to perform maintenance or other functions and you don’t want to permanately alter your databases. You can read more about the process from Microsoft.