[dba-SQLServer] Viewing and Modifying stored procedures

Yes, this is *exactly* how I work too. 

Jennifer, you may be correct that the ALTER syntax is not technically a part 
of the SP at some lower level. Conceptually, when I made the transition from 
Access to SQL Server I personally found it easier to think of it that way; I 
was hoping to help you over that "hump".

Ron


----------------------  Original Message:  ---------------------
From:    "Jennifer Gross" <jengross at gte.net>
To:      "SQL Server List" <dba-sqlserver at databaseadvisors.com>
Subject: Re: [dba-SQLServer] Viewing and Modifying stored procedures
Date:    Wed, 27 Jun 2007 21:26:45 +0000

> 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
> 
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>