September 19, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

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.

 

 


Sitemap | Contact Us

Rocket Fuel