Managing Stored Procedures

DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Managing Stored Procedures

  1. #1
    Randy Pearson Guest

    Managing Stored Procedures

    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?

    -- Randy

  2. #2
    D. Patrick Hoerter Guest

    Re: Managing Stored Procedures


    +AD4-1. How does one print the source code for one or more stored procedures
    +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

    +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)
    VarcharField +AD0- +AEA-varMyVarcharParam
    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.

    D. Patrick Hoerter

  3. #3
    Randy Pearson Guest

    Re: Managing Stored Procedures

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center