Temporary Stored Procedures

Temporary stored procedures are related to stored procedures as temporary tables are related to tables. You use them when you expect to reuse the execution plan of a stored procedure within a limited time frame. Although you can achieve the same functionality with a standard user -defined stored procedure, temporary stored procedures are a better solution because you do not have to worry about maintenance issues (such as dropping the stored procedure).

Temporary stored procedures reside in the tempdb database and must be named with the prefix #. You create them in the same way you create user-defined stored procedures. The only change is the use of a # as a name prefix. This prefix signals the server to create the procedure as a temporary stored procedure. This kind of stored procedure can only be used from the session in which it was created. When the session is closed, it will be dropped automatically. This behavior indicates why this type of stored procedure is often also referred to as a private temporary stored procedure.

The following code example creates a private temporary stored procedure:

Create Procedure #dbo.ap_Eq!d_List @Make varchar(50), @Model varchar(50) as Select Eqld from dbo.Equipment where Make = @Make and Model = @Model return

Note

Sometimes, all user-defined stored procedures in tempdb are referred to as temporary stored procedures. This is incorrect because tbere are major differences between tbe two. For example, user-defined stored procedures stored in tbe tempdb database are accessible to all autborized users and are not limited to tbe session in wbicb tbey were created. These stored procedures stay in tempdb until tbe server is sbut down, at wbicb time tbe complete content of tempdb is flusbed.

Global Temporary Stored Procedures

Global temporary stored procedures are related to temporary stored procedures as global temporary tables are related to private temporary tables. They also reside in the tempdb database, but they use the prefix ##. You create them in the same way you create temporary stored procedures. The only difference is that they are visible and usable from all sessions. In fact, permissions are not required and the owner cannot even deny other users access to them.

When the session that has created the procedure is closed, no new sessions will be able to execute the stored procedure. After all instances of the stored procedure already running are finished, the procedure is dropped automatically.

The following code example creates a global temporary stored procedure:

Create Procedure ##apEquipment_Insert @Make varchar(50), @Model varchar(50), @EqType varchar(50) as declare @EqTypeId smallint select @EqTypeId = EqTypeId -- This is OK in a perfect world, from dbo.EqType -- but it is based on the Where EqType = @EqType -- unreasonable assumption that -- you can identify the key using -- the description. Insert dbo.Equipment (Make, Model, EqTypeld) Values (@Make, @Model, @EqTypeld}

Remote Stored Procedures

This type is actually a user -defined stored procedure that resides on a remote server. The only challenge implicit in this type of stored procedure is that the local server has to be set to allow the remote use of stored procedures.

Run the following code on the local server:

EXEC sp_addlinkedserver LocalServer, N'SQL Server' EXEC sp_addlinkedserver RemoteServer

EXEC

sp_configure 'remote access', 1 RECONFIGURE

Stop and restart the local server.

To set up access for a login "sa" from the local server to remote server, run the following code on the remote server. It is critical that you log in using SQL Server Authentication and it is assumed that the logins on different servers have the same password.

EXEC sp_addlinkedserver RemoteServer, local EXEC sp_addlinkedserver LocalServer EXEC sp_configure 'remote access', 1 RECONFIGURE GO EXEC sp_addremotelogin LocalServer, sa, sa GO

Stop and restart the remote server.

Using the "sa" login, you can now execute a stored procedure on the remote server from the local server.

Tip

Microsoft, in fact, considers this mechanism as a legacy of older versions of SQL Server. Heterogeneous queries are the recommended way to execute stored procedures or access tables on other servers.

Flylib.com

Category list

Similar pages

Temporary Stored Procedures