What is Temp Stored Procedures? – Interview Question of the Week #294

Question: What is Temp Stored Procedures?

Answer: Temp stored procedures are kind of the stored procedures that are created with the help of the # at the beginning of the name and only available until the SQL Server restarts.

When I go for SQL Server Performance Tuning Comprehensive Database Performance Health Check, one of the tasks which I do is to help clients to tune their SQL Server queries and stored procedures. While we created the stored procedure, the challenge which we face is that we ended up creating many stored procedures that were really not useful.

When we create a new stored procedure based on the older stored procedure, we often give names as Temp, etc at the end of the name of the stored procedure. It would be best if we create a temp stored procedure with the # sign before it so it is easier for us to keep our main repository of the stored procedures clean.

Here is how you can create a temporary stored procedure that you can access in a single session.

CREATE PROCEDURE #TempSP
AS
SELECT 1 AS SPName
GO

Now you can use the same SP just like a normal SP and execute it with the following command.

EXEC #TempSP

You can use the same SP to compare with other SPs with the help of the execution plan. The only limitation is that when you create the temporary SP with a single # sign, it is only accessible and available in the same session (or query window in SSMS).

However, if you want to use the SP in your entire server, you can create a global stored procedure with two # signs as described in the following example.

CREATE PROCEDURE ##GlobalSP
AS
SELECT 1 AS SPName
GO

Well, that’s it. Now you can use this SP across your server. This way you do not pollute the stored procedure repository. Once you know the version of the SP you want to use, you can convert your temporary SP to permanent SP. Remember, all the temporary SP are removed when your session closes (or you restart your SQL Server services).

Reference: Pinal Dave (https://blog.sqlauthority.com)