January 26, 2021
Hot Topics:

Quick Start Guide to SQL Server 7 -- Part 4

  • By Karl Moore
  • Send Email »
  • More Articles »

Stored Procedures (SPs) are chunks of code that reside on the server. Each SP can perform a wide variety of tasks.

You can call a stored procedure from within Visual Basic, perhaps passing a required argument or accepting a 'return value'.

Let's look at a few of the advantages using stored procedures gives us:

  • Easy Maintainability Stored procedures can get very complex, allowing you to moving difficult code from the client program and place it on the server. This allows for easier maintenance
  • Network Traffic Asking the server to do a chunk of work with a stored procedure, as opposed to the client executing individual server statements can really reduce network traffic
  • Greater Security - Some companies only allow developers permission to execute and retrieve database information via stored procedures. This means less chance of mishaps, as direct access to the underlying tables is never granted

Over the next few pages, we're going to cover four common stored procedures, alongside calling Visual Basic code (without error handling for simplicity). In addition, we'll also be demonstrating output parameters.

All of this code can be used with the sample Pubs database that ships with SQL Server. Don't forget to ensure the user ID you use has execution permissions for the stored procedure. Also, remember to add a project reference to the ADO Library before running the Visual Basic code.

To create a Stored Procedure:

  • View your Database in Enterprise Manager (as before)
  • Right-click on the 'Stored Procedures' icon, selecting 'New Stored Procedure'
  • Type your stored procedure statement
  • Click OK to Save

To edit a Stored Procedure:

  • View your Database in Enterprise Manager (as before)
  • Click the 'Stored Procedures' icon
  • Double-click on your stored procedure
  • Edit as required
    • You can't change the name of a stored procedure once you've created it. To do this, you'll have to completely recreate it, then delete the original
  • Click OK to Save

Page 4 of 10

This article was originally published on November 20, 2002

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date