Stored Procedures
A stored procedure is a
A stored procedure is a subroutine available to applications accessing a subroutine available to applications accessing a relational database system.relational database system.
Typical uses for stored procedures include data validation (integrated into the database) or access Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures are used to co
control mechanisms. Furthermore, stored procedures are used to co nsolidate and centralize logicnsolidate and centralize logic that was originally implemented in applications.
that was originally implemented in applications. Large or complex processing that might Large or complex processing that might require therequire the execution of several SQL statements is moved into stored procedures, and all applications call the execution of several SQL statements is moved into stored procedures, and all applications call the procedures only.
procedures only.
A stored procedure is a precompiled collection of SQL statements and optional control-of-flow A stored procedure is a precompiled collection of SQL statements and optional control-of-flow statements, similar to a macro. Each
statements, similar to a macro. Each database and data provider supports stored procdatabase and data provider supports stored proc eduresedures differently. Stored procedures offer the following benefits to your database applications:
differently. Stored procedures offer the following benefits to your database applications:
Performance
Performance——Stored Procedures are usually more efficient and Stored Procedures are usually more efficient and faster than regular SQL faster than regular SQL queriesqueries because SQL statements are parsed for syntactical accuracy and precompiled by the DBMS when the because SQL statements are parsed for syntactical accuracy and precompiled by the DBMS when the stored procedure is created. Also, combining a large number of SQL statements with conditional logic stored procedure is created. Also, combining a large number of SQL statements with conditional logic and parameters into a stored procedure
and parameters into a stored procedure allows the procedures to perform queries, make decisions,allows the procedures to perform queries, make decisions, and return results without extra
and return results without extra trips to the database server.trips to the database server.
Maintainability
Maintainability——Stored Procedures isolate the lower-level database structure from tStored Procedures isolate the lower-level database structure from t he application.he application.
As long as the table names, column names, parameter names, and types do not change from what is As long as the table names, column names, parameter names, and types do not change from what is stated in the stored procedure, you
stated in the stored procedure, you do not need to modify do not need to modify the procedure when changes are made tothe procedure when changes are made to the database schema. Stored procedures are also a way to support modular SQL programming
the database schema. Stored procedures are also a way to support modular SQL programming because after you create a procedure, you and other users can reuse that procedure without because after you create a procedure, you and other users can reuse that procedure without knowing the details of the tables involved.
knowing the details of the tables involved.
Security
Security——When creating tables in a When creating tables in a database, the Database Administrator can set EXECUTEdatabase, the Database Administrator can set EXECUTE permissions on stored procedures without granting SELECT, INSERT,
permissions on stored procedures without granting SELECT, INSERT, UPDATE, and DELETEUPDATE, and DELETE permissions to users. Therefore, the data in
permissions to users. Therefore, the data in these tables is protected from users who these tables is protected from users who are not usingare not using the stored procedures.
the stored procedures.
Stored procedures are similar to user-defined functions. The major difference is that functions can be Stored procedures are similar to user-defined functions. The major difference is that functions can be used like any other expression within
used like any other expression within SQL statements, whereas stored procedures must be invokedSQL statements, whereas stored procedures must be invoked using the CALL statement.
using the CALL statement.
The syntax for creating a Stored Procedure is as follows:
The syntax for creating a Stored Procedure is as follows:
CREATE
CREATE PROCEDUREPROCEDURE <ProcedureName> <ProcedureName>
@<Parameter1> <datatype>
@<Parameter1> <datatype>
61
61 Creating Creating and and using using Stored Stored ProceduresProcedures
Tutorial: Database Communication in LabVIEW Tutorial: Database Communication in LabVIEW
…
…
Example: Create a Stored Procedure Example: Create a Stored Procedure
This Procedure gets Customer Data based on a specific Order Number.
This Procedure gets Customer Data based on a specific Order Number.
IF
IF EXISTSEXISTS ((SELECT nameSELECT name FROM
FROM sysobjectssysobjects WHERE name
WHERE name == 'sp_CustomerOrders''sp_CustomerOrders' AND
CREATE PROCEDURE sp_CustomerOrders CREATE PROCEDURE sp_CustomerOrders
@OrderNumbe
@OrderNumber r varcharvarchar((5050)) AS
AS
/*---Last
Last Updated Updated Date: Date: 2009.11.032009.11.03 Last
Last Updated Updated By: By: [email protected]@hit.no Description:
Description: Get Get Customer Customer Information Information from from a a specific specific Order Order NumberNumber ---*/
select @CustomerId == CustomerId from [ORDER] where CustomerId from [ORDER] where OrderNumbeOrderNumberr == @OrderNumber @OrderNumber
select CustomerId
select CustomerId,, FirstName FirstName,, LastName LastName,, [Address] [Address],, Phone from CUSTOMER where Phone from CUSTOMER where CustomerId
Example: Using a Stored Procedure Example: Using a Stored Procedure Using the Stored procedure like this Using the Stored procedure like this exec
exec sp_Customersp_CustomerOrdersOrders '10002''10002' gives the following result:
gives the following result:
62
62 Creating Creating and and using using Stored Stored ProceduresProcedures
Tutorial: Database Communication in LabVIEW Tutorial: Database Communication in LabVIEW
12.1
12.1 Exercises Exercises
Run the Stored Procedure created above from LabVIEW.
Run the Stored Procedure created above from LabVIEW.
63 63