Creating Local Temporary Stored Procedures in SQL Server

Q

How To Create a Local Temporary Stored Procedure in SQL Server Transact-SQL?

✍: FYIcenter.com

A

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 'fyicenter.com';
GO
Welcome to moc.retneciyf

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

 

⇒Using Stored Procedures in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials

2016-12-28, 359👍, 0💬

General MySQL Oracle SQL Server