Thursday, 19 February 2015

Temporary Stored Procedures in SQL Server 2008

Temporary Stored Procedures are same as the normal Stored Procedures. 
There are two kinds of temporary Stored Procedures, 
local (#)
Global.(##)
Temporary Stored Procedures are created just like any other SP but the name must be prefixed with a hash (#) for a local temporary SP and two hashes (##) for a global temporary Stored Procedure.


A local temporary Stored Procedure is only with in  the current session and is dropped when the session is closed 

CREATE PROCEDURE [dbo].[#TempProcedure] 
 @x INTEGER,
 @y INTEGER,
 @Z INTEGER OUTPUT
AS
 SET @Z=@x+@y
 SELECT @Z


To execute
declare @aaa integer
exec #TempProcedure 2,2,@aaa output



When a execute the same stored procedure in different session
then i get an error as follows(i,e in new query window)

Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure '#TempProcedure'.


Global temporary Stored Procedure
A global temporary Stored Procedure is visible to all sessions and is dropped
when the session of the user that created it is closed.


CREATE PROCEDURE [dbo].[##TempProcedure]
 @x INTEGER,
 @y INTEGER,
 @Z INTEGER OUTPUT
AS
 SET @Z=@x+@y
 SELECT @Z


To execute
declare @aaa integer

exec ##TempProcedure 2,2,@aaa output






Where can we find this temporary stored procedures


No comments:

Post a Comment