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

User-defined functions in SQL

User defined functions are stored subprograms that must return a value with return statements. User defined functions are classified functions.

• Scalar function: This are the user defined functions that return a single value. 

• Valued functions: This are user defined functions that returns a table.  Table valued functions are classified into inline table valued functions and multistatement table values statement.

• Inline Table valued functions:This are the table valued functions that contain a single statement i.e the return statement with a select statement within it, whose results will be converted inline as a table.

Inline table valued function must not contain the keyword begin and end.

• Multistatement valued functions: This are table value functions that contain multiple statements. In case of multistatemnet table values function you have to create a table in a function manual, insert rows into it and return it manual.