I learned something new today. In SQL Server you can create temporary stored procedures that automatically go away when your session closes.
Here is a good article on Temporary Stored Procedures.
I already knew that you could create temporary tables, but I hadn't known that the same applied to stored procedures.
Temporary tables are created by starting the table name with a # sign.
Example:
create table #mytbl
(
[Name] varchar(50),
[Age] int
)
The same trick is used for stored procedures.
Example:
create procedure #addName
(
@Name varchar(50),
@Age int
)
begin
insert into #myTbl([Name], [Age]) values(@Name, @Age)
end
This is really useful for scripts that you use for different tasks that contain repeated code that you hate duplicating but you don’t really want to permanently load into the database.
For example, say you have a script that runs on your build and deployment that fixes up any server logins that may have become broken from a rebuild or some other action. You might have a script that looks like the following.
declare @name varchar(100)
set @name = 'NT AUTHORITY\NETWORK SERVICE'
-- check to see if login exists
if exists(select name from master.sys.syslogins where name = @name)
begin
-- if login exists, check to see if user exists on db
if not exists(select name from sys.sysusers where name = @name)
begin
-- if user does not exist on db, then add it and add roles
exec sp_grantdbaccess @name
exec sp_addrolemember 'db_owner', @name
print @name
end
else
begin
exec SP_CHANGE_USERS_LOGIN 'AUTO_FIX', @name, NULL, '*****'
end
end
set @name = 'APPSVR\svnprojects'
-- check to see if login exists
if exists(select name from master.sys.syslogins where name = @name)
begin
-- if login exists, check to see if user exists on db
if not exists(select name from sys.sysusers where name = @name)
begin
-- if user does not exist on db, then add it and add roles
exec sp_grantdbaccess @name
exec sp_addrolemember 'db_owner', @name
print @name
end
else
begin
exec SP_CHANGE_USERS_LOGIN 'AUTO_FIX', @name, NULL, '*****'
end
end
set @name = 'APPSRV\agentmgr'
-- check to see if login exists
if exists(select name from master.sys.syslogins where name = @name)
begin
-- if login exists, check to see if user exists on db
if not exists(select name from sys.sysusers where name = @name)
begin
-- if user does not exist on db, then add it and add roles
exec sp_grantdbaccess @name
exec sp_addrolemember 'db_owner', @name
print @name
end
else
begin
exec SP_CHANGE_USERS_LOGIN 'AUTO_FIX', @name, NULL, '*****'
end
end
This contains lots of duplicate code which is more difficult to read is also error prone if you need to update the code that is performed for each user.
Now consider the updated code using a temp stored procedure.
create procedure #addLogin
(
@name varchar(256),
@role varchar(256),
@password varchar(256)
)
as
begin
-- check to see if login exists
if exists(select name from master.sys.syslogins where name = @name)
begin
-- if login exists, check to see if user exists on db
if not exists(select name from sys.sysusers where name = @name)
begin
-- if user does not exist on db, then add it and add roles
exec sp_grantdbaccess @name
exec sp_addrolemember @role, @name
print @name + ' granted access'
end
else
begin
exec SP_CHANGE_USERS_LOGIN 'AUTO_FIX', @name, NULL, @password
print @name + ' fixed.'
end
end
end
go
exec #addLogin @name = 'NT AUTHORITY\NETWORK SERVICE', @role='db_owner', @password='*****'
exec #addLogin @name = 'APPSVR\svnprojects', @role='db_owner', @password='*****'
exec #addLogin @name = 'APPSRV\agentmgr', @role='db_owner', @password='*****'
go
Much shorter and much easier to read. the #addLogin stored procedure will automatically be dropped when the session is closed so I don’t have to worry about polluting my database with utility procedures that don’t need to hang around.