dcsimg
June 24, 2018
Hot Topics:

Quick Start Guide to SQL Server 7 -- Part 4

  • November 19, 2002
  • 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



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

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

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.

Sitemap

×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date