Temporary Stored Procedures in SQL Server

sqlserver2012

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s