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?

    TIA,
    -- Randy

  2. #2
    D. Patrick Hoerter Guest

    Re: Managing Stored Procedures

    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



  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
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

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