Stored procedures have a segment of code which contains declarative or procedural SQL statement. A stored procedure is resided in the catalog of the database, we can invoke (call) it from a program, stored procedure or even from a trigger.
Stored Procedure contains sql statements like insert, update and delete. In addition stored procedure supports If and while statements.
Advantages:
Once we created Stored Procedure then it can be reused over and over again by multiple applications.
Stored Procedure increase the performance of the application because once it compiled successfully then it’s stored in database catalog. When applications call them, they generally execute faster when compared with un-compiled SQL Statements which are sent from the applications.
Network Traffic between application server and database server is also signification reduced when compared with un-compiled SQL Statements which are sent from the applications.
SyntaxCREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE ENCRYPTION RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
owner:
Is the name of the user ID that owns the stored procedure. owner must be either the name of the current user or the name of a role that a current user is a member.
Is the name of the new stored procedure, procedure name cannot exceed 128 characters.
Is a parameter in the procedure. One or more parameters can be declared in a CREATE PROCEDURE statement. The value of each declared parameter must be supplied by the user when the procedure is .A stored procedure can have a maximum of 2,100 parameters. Specify a parameter name using an at sign (@) as the first character.
OUTPUT:
Indicates that the parameter is a return parameter.
;number:
Is an optional integer used to group procedures of the same name so they can be dropped together with a single DROP PROCEDURE statement.
No comments:
Post a Comment