Stored sub programs in SQL

• Stored subprograms are the programs that are permanently stored in the database. Execution of stored sub-programs will be fast compare to independent SQL statements.

• Hence to perform any operation again database from the front end application like .Net it is recommended to use stored sub programs than independent SQL statements to improve the performance of application.

•   Before executing an independent SQL statements , SQL server has to perform parsing execution plan generation and cost estimation. During Parsing it will verify syntax of statement, availability of object refers in the statement, permissions of current user to perform specified operations on specified object and data type compatibility. As all this steps takes time execution will be slow and it affects performance of application.
• Stored procedures are classified into :

• Stored procedures
• User defined procedures
• Triggers

Stored Procedure: Stored procedures are the stored subprograms that cannot return a value with return statement. A stored procedure can contain return statemnet without a value to return and purpose of this is to exit from stored procedure.

 Syntax:
create procedure<procname>[(<parameters>)]
[with encryption] as begin
<statements>
end