2 comments on stored procedures

Stored procedures are compiled (the first time they are run) and therefore should execute faster than the raw t-sql code.  They also allow the use of variables, so that the same stored procedure can be reused:

SELECT DISTINCT City FROM Person.Address where StateProvinceID=80;

and write a stored procedure as

CREATE PROCEDURE dbo.GetCities
    @StateProvinceID int
AS
SELECT DISTINCT City
FROM Person.Address
WHERE StateProvinceID=@StateProvinceID;
GO
now you can call this stored procedure as
EXEC dbo.GetCities @StateProvinceID=80;
EXEC dbo.GetCities @StateProvinceID=50;
EXEC dbo.GetCities @StateProvinceID=75;
This example was stolen from Kendra Little of BrentOzar.com (http://www.brentozar.com/archive/2013/06/optimize-for-unknown-sql-server-parameter-sniffing/).
This is convenient to run and for programmers, but a problem is that the procedure is not complied until first run, so there is a  “stored parameter” is “(80)”. The first time the “run time parameter” is right on, the next times, not so good.
Another use of stored procedures is mentioned in http://www.karaszi.com/sqlserver/info_change_server_name.asp.  The name of the server is stored in sysservers table in the master database.  If you change the name of the server (for example in the windows domain), you need to update this value.  There is a stored procedure (actually two) to do this
EXEC sp_dropserver '<old_name>' 
GO 
EXEC sp_addserver '<new_name>', 'local' 
GO
Why not just change the entry in the table?  One – you will forget where the table and where  the value is stored – what if there are several locations?.  Two – you will have the master database spread wide-open while you are making the changes.  OK if you are restarted in single-user mode, but bad if you are prepping or restarting a migrated server – when the machine might be accepting other transactions.  The stored procedure with run inside the server and all the locks\latches will take care of consistency.
Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.