[dba-SQLServer] Viewing and Modifying stored procedures

Jennifer Gross jengross at gte.net
Wed Jun 27 16:22:45 CDT 2007


To keep the thread complete, here is some more helpful information from
David Lewis: (thanks David)

If you open the sproc (right click, modify, or generate script to
clipboard or to new query window or any other method as previously
noted) an ALTER PROC script is automatically generated, as you noted.

You can comment out the autogenerated parts using /*          */ tags,
then play around with the sql script to your heart's content.  To
execute all or part of the sql highlight it and press F5.  If the ALTER
PROC lines have been deleted or commented out, then you don't need to
highlight the sql to execute -- you can simply press F5 and the entire
screen will execute.  If you don't comment out the ALTER PROC portion
before pressing F5 and you HAVE altered the sql script, then the old
procedure will be replaced with the new one.

If you have a long script, or a screen full of many scripts that are
related and are perhaps a work in progress, you can save them in two
ways.  1) File>>Save As   will save it as an .sql file, which is pretty
much just a text file of the sql.  You can also create a sproc with the
mess of scripts and codes just as they are.  I often do that, knowing
that it is a work in progress that I want to come back to, and although
it may be listed under Stored Procedures by EM, it is a sproc in name
only and will not 'work' if executed.

Sometimes when I know there is a piece of code from a sproc that I could
use to base a new query on, I'll open it, copy the section I want to the
clipboard, CTRL-N for a new query window, paste the snippet in there and
go to work on it.  When it is done and I want to turn it into a new view
or sproc, I just add CREATE VIEW or CREATE PROC to the heading along
with anything else needed, and hit F5.  I shut down the other query
window without executing it and the original sproc will remain
unchanged.

HTH.  D. Lewis





More information about the dba-SQLServer mailing list