Temporary stored procedures are related to stored procedures as temporary tables are
Temporary stored procedures reside in the
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
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
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 are related to temporary stored procedures as global temporary tables are
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}
This type is actually a
Run the following code on the local server:
EXEC sp_addlinkedserver LocalServer, N'SQL Server' EXEC sp_addlinkedserver RemoteServer
EXECsp_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.
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.