Posted by Erland Sommarskog on 11/20/07 22:40

Allie (fakeprogress@gmail.com) writes:
> So. I'm using MS SQL Server 2005 Management Studio Express. I have a
> table that was created via an existing .sql file. Included were a
> bunch of stored procedures. I went in to re-format these procedures.
> (They were written in haste by another programmer. It is my task to go
> back and improve their readabilities.) However, I am having difficulty
> figuring out how to save the changes to these stored procedures. Thus
> far, I have been individually saving each stored procedure in its
> own .sql file. This is obviously not the way to go... but it's a
> temporary solution (so as not to lose the changes I have made to
> dozens of procedures).

I think that is exactly the way to go. You should also put the files under
source control.

> Can someone please explain to me how I commit these changes to the
> database?

But that's not saving. You save a file to the file system, and as I said,
you also put it under version control.

To change the database what's in the database is more akin to a compilation.
To make this easy to handle, I suggest that you have this in your files:

IF object_id('my_sp') IS NULL
EXEC ('CREATE PROCEDURE my_sp AS SELECT 1')
go
ALTER PROCEDURE my_sp -- real code follows here

Then it doesn't matter when you run the file, whether the procedure
exists or not.

And to run the file from Mgmt Studio, you can clich the green arrow,
or press CTRL/E or F5.

When you need to load several files, this is more convenient to do
from a BAT file and use SQLCMD instaead.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]