Managing Stored Procedures

[Randy Pearson]

I'm fairly new to T-SQL development and so far have been 'writing' stored
procedures using the query analyzer. If I want to view/edit a procedure I
wrote earlier, I use the Enterprise Manager and bring up the properties dialog.

This is starting to seem like less than a thrilling development environment.
So I have a few questions:

1. How does one print the source code for one or more stored procedures (other
than by copy/paste into Notepad)?

2. What other tools that come with SQL Server 7 are good for editing and
managing SPs and triggers?

3. Any 3rd party products that lots of people are using?

TIA,
-- Randy

[D. Patrick Hoerter]

Randy,

+AD4-1. How does one print the source code for one or more stored procedures
(other
+AD4-than by copy/paste into Notepad)?

Use Query Analyzer, and run sp+AF8-helptext +AHs-procname+AH0-
That'll give you the source code that you can print out, no copy/paste
required.

+AD4-2. What other tools that come with SQL Server 7 are good for editing and
+AD4-managing SPs and triggers?

It's hard to beat what they've provided - the syntax coloring is great. Only
thing is, it's +AF8-all+AF8- they've provided.
What I do is build the procs in Query Analyzer, as an interactive debugger,
and use generous amounts of PRINT and SELECT as debugging aids.

Here's how I do it:

Let's say there's a proc I'm doing, and it's supposed to have an int
parameter, and a varchar parameter.
Do this: (so you can write correct code, variable names and all)

--Declare the same parameter's you'll use in your CREATE PROCEDURE statement
DECLARE +AEA-iMyIntParam int, +AEA-varMyVarcharParam varchar(50)

--Set param values here
SET +AEA-iMyIntParam +AD0- 1
SET +AEA-varMyVarcharParam +AD0- 'Test'

--Actual code (CREATE PROCEDURE goes here later)
UPDATE
MyTable
SET
VarcharField +AD0- +AEA-varMyVarcharParam
WHERE
IntField +AD0- +AEA-iMyINtParam

This way, when you're ready, you just add:
CREATE PROCEDURE +AHs-procname+AH0- (+AEA-iMyIntParam int, +AEA-varMyVarcharParam
varchar(50)) AS
.... in the right place, and execute, after commenting the DECLARES and SETs
at the top.

Now, there is an integrated debugger in Visual Studio (works from VB), but
it's often a real pain to configure. Search MSDN for details on that bugger.

Regards,
D. Patrick Hoerter

[Randy Pearson]

Thanks for the input. I did discover sp_helptext shortly after posting my
questions, and that does help a bit.

One thing I don't like about Enterprise Manager is that the properties windows
are all modal. So I can't open two or more different SPs at the same time.
Sort of a pain compared to VisualFoxPro or VB's IDE.

-- Randy