Stored Procedure Basics

A stored procedure is a precompiled collection of SQL statements that can take and/or return user-supplied parameters. You can place any number of SQL statements into a stored procedure. Also, stored procedures can be nested, up to 16 levels deep, so that one procedure can call another. Stored procedures are also available to triggers within your database.

Benefits of Stored Procedures

The main benefit of stored procedures is their performance. Stored procedures are simply the fastest way to execute your data-driven code. This is because the SQL statements are precompiled and stored in memory on the database server. It's much faster to encompass all your database processing logic into a stored procedure and have a single round-trip from the browser to the server and back than to have lots of database calls that place traffic on the network. In the client/server days, there was typically much traffic between the client machine and the database. In the Web world, this traffic is more often seen between the Web server (perhaps executing Web pages) and the database.

Stored procedures can be used as a form of security. For example, users can be given access to the data via stored procedures only and not directly to the tables in the database. This helps ensure that users cannot perform restricted operations on the data. All their database access has to go through the stored procedures, which have predefined operations.

Stored procedures are also objects. In this way, you can consider them components that you can use to package some of your business logic. Other applications can then reuse this business logic, which can help speed development and promote reuse across an organization.

Stored Procedure Syntax

Stored procedures can be created using the CREATE PROCEDURE syntax in SQL Server. The full syntax is

 CREATE PROCEDURE [owner.]procedure_name[;number]     [(parameter1 [, parameter2]…[parameter255])] [{FOR REPLICATION} | {WITH RECOMPILE}     [{[WITH] | [,]} ENCRYPTION]] AS sql_statements 

where

  • procedure_name is the name of the new stored procedure
  • ;number is an optional integer used to group procedures of the same name so that they can be dropped together with a single DROP PROCEDURE statement
  • parameter has the form

              @parameter_name datatype  [= default][OUTPUT] 

    where

    • parameter specifies a parameter in the procedure
    • datatype specifies the datatype of the parameter
    • default specifies a default value for the parameter
    • OUTPUT indicates the parameter is a return parameter
  • sql_statements specifies the actions that the procedure is to take. Any number and type of SQL statements can be included in the procedure

In addition to creating stored procedures, you might also want to rename them or drop them from the database. To rename a stored procedure within SQL Server, use the sp_rename system stored procedure as follows:

 sp_rename old_procedure_name, new_procedure_name 

To drop a stored procedure within SQL Server, you use the DROP PROCEDURE statement as follows:

 DROP PROCEDURE procedure_name 

Both of these commands can be executed either from the SQL Enterprise Manager (part of SQL Server) or from the SQL pane within Visual InterDev. If you are using the SQL Enterprise Manager, you can choose the SQL Query Tool by choosing SQL Query Tool from the Tools menu.

Another system stored procedure within SQL Server that you might find useful is the sp_help stored procedure. This procedure will give you a report on a stored procedure, including the name and owner of the stored procedure along with the parameter names, data types, lengths, and precisions of any parameters that the stored procedure has defined. You call the sp_help stored procedure as follows:

 sp_help procedure_name 

Note
Within Visual InterDev, you can find most of this information by simply expanding the plus sign (+) next to the stored procedure in the Data View window. This will open up a listing of all the parameters, including resultset columns, that are contained in the stored procedure. You can then view the properties of these parameters by right-clicking the relevant parameter and choosing Properties from the context menu.

Here's a few rules to bear in mind when creating stored procedures. These rules apply to SQL Server 6.5 databases only.

  • The CREATE PROCEDURE definition can include any number and type of SQL statements, with the exception of the following: CREATE VIEW, CREATE DEFAULT, CREATE RULE, CREATE TRIGGER, and CREATE PROCEDURE.
  • You can create other database objects within a stored procedure as long as they are created before they are referenced in the procedure.
  • The maximum number of parameters in a stored procedure is 255.
  • The maximum number of local and global variables in a procedure is limited only by available memory.
  • You can reference temporary tables within a stored procedure.


Programming Microsoft Visual InterDev 6. 0
Programming Microsoft Visual InterDev 6.0
ISBN: 1572318147
EAN: 2147483647
Year: 2005
Pages: 143

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net