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
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
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
No comments:
Post a Comment
Thank you for visiting my blog