MS SQL Server Interview Question:

How To Create a Local Temporary Stored Procedure?

Submitted by: Administrator

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
Submitted by: Administrator