MS SQL Server Concepts and Programming Question:
Download Questions PDF

How To Create a Local Temporary Stored Procedure?

Answer:

A local temporary stored procedure is a special stored procedure that:

* Is created like a normal (permanent) stored procedure with the name prefixed with a number sign (#).
* Are only valid in the same client session where it was created.
* Will be deleted when creating session is terminated.

This tutorial exercise here creates two stored procedures, one is permanent and the other is local temporary:

DROP PROCEDURE Hello;
DROP PROCEDURE #Hello;
GO

CREATE PROCEDURE Hello
@url nvarchar(40)
AS
PRINT 'Welcome to ' + REVERSE(@url);
GO

CREATE PROCEDURE #Hello
@url nvarchar(40)
AS
PRINT 'Welcome to ' + @url;
GO

EXECUTE Hello 'globalguideline.com';
GO
Welcome to globalguideline

EXECUTE #Hello 'globalguideline.com';
GO
Welcome to globalguideline.com

Download MS SQL Server Interview Questions And Answers PDF

Previous QuestionNext Question
How To Receive Output Values from Stored Procedures?Can Another User Execute Your Local Temporary Stored Procedures?