SQL and Database: Stored Procdures

useful info
1

So, I’ve been asked to modify some stored procedures. I don’t really know anything about them, so I just right clicked on the existing one and selected “Modify”. I made the changes to the variables and queries that were requested, but since these are supposed to be in addition to the existing procedures rather than actually replacing them, I didn’t run them, I just saved them as queries like I normally do for my boss’ review.

I’m pretty sure that some of the stuff that got inserted at the top should probably go (Such as ALTER PROCEDURE [procedure_name] and there is a folder under the existing folders called Parameters where I will probably want to update the information for the changes to the variables that I made.

Can anyone point me to some good tutorials on stored procedures? I did some searching and came up with these from Technet, but I wanted to make sure they were the best ones to read before I spent a lot of time on them.

2

Taking a step back:

We keep all our stuff in source control, and want that to be the One True Source for all our database stuff. So I almost always set up my “stored procedure creation scripts” like so:

IF object_id('FULL_QUALIFIED_NAME') IS NULL
BEGIN
	EXEC ('create procedure FULL_QUALIFIED_NAME as select 1');

	EXEC ('grant execute on object::FULL_QUALIFIED_NAME to ROLE;');
END
GO

ALTER PROCEDURE FULL_QUALIFIED_NAME PARAMS
AS
BEGIN
	SET NOCOUNT ON;

DO STUFF

END;

Reason being, I can now run this script on any database, whether the SP exists or not, and it’ll be set up properly. Otherwise, I have to change CREATE to ALTER or vice versa (depending on the state of the database) and…ick.

In places where we’re not doing that, if I have to modify an SP I’ll right-click it in SSMS, Script Stored Procedure as -> Alter To -> New Window (I’ve never used your method, but it appears to be the same net result; I try to stay away from the “modify” menu items in SSMS as there are bugs with some of the GUI-based designers). Then I comment out all the bits for actually doing the ALTER itself, make my changes, give it a few test runs to make sure it works, then uncomment what I’d commented earlier (highlight the lines, then CTRL-K, CTRL-C and CTRL-K, CTRL-U are handy here - Edit menu, Advanced) and run it.

In places where we have done the create/alter trick above, I’ll do something similar, but using my file instead of scripting the SP within SSMS.

For any change to a stored procedure (or any other object/schema) that already exists, you’ll execute an ALTER. You’re changing the code of the SP itself. Just like ALTER TABLE changes the structure of your tables.

Unfortunately, I do not have any tutorials on SPs handy. For the most part, they’re pretty easy, treat them as a way to encapsulate, parameterize & optimize queries you’ll need to use in multiple places.

1 Like
3

As with most things in SQL, we could spend lots of time on this. To emphasize what Dak said, there should be some sort of source control for your SPs. If you overwrite one, there may not be a way to get back to the original without restoring a backup of the database.

They’re basically just SQL that lives in the data dictionary. Since it’s already compiled, it loads faster.

Like Dak, I don’t really have anything for learning about stored procedures, but I found a short video at SQL Server Central that hits most of what you need to know to get started: http://www.sqlservercentral.com/articles/Video/63805/ . It will require you to register if you haven’t already. It’s free. You can probably find similar stuff on YouTube.

There are probably places where the standard is to drop an existing stored procedure and then recreate it, but we have fairly tight controls on who has permissions to them. It’s just easier to backup the existing SP do an ALTER PROCEDURE so you don’t have to recreate all the permissions. I’ve seen places where they’ll renanme an existing SP and load in the new one. If it fails to function as advertised, it gets dropped and the old one is renamed given its original name. Seems like a lot of hell to go through to avoid doing real testing.

EDIT: Ooooh, look! I have an avatar!

4

Thanks for the information! I don’t actually have permission to create or alter any stored procedures, but I’m pretending to. I took the one I modified and sent it to my boss to see if I did it right. The query part works, at least.

5

With plan caching built into SQL Server for a long time now, and other basic optimizations, the performance advantage of SPs is negligible nowadays except in some weird edge cases.