INTRODUCTION TO STORED PROCEDURES
- Reusable code module stored in database
- Contain T-SQL that perform operations on data using DML or objects using DDL
- Contain logic error handling statements
- Can accept parameters-Input parameters, output parameters
- Return multiple results
- 3 types of stored procedures-user define, extended & system
- Recompile if change in schema or in table structure
- DROP PROCEDURE { [ schema_name. ] procedure } [ ,...n ] like
DROP PROCEDURE dbo.usp_GetCategory</p>
Benefits
- Fast, pre-complied & stored in databases ( as long as restart sql server )
- Reduce network traffic
- Enhance security
- Modular programming can save time & maintainability
- Can be encrypted
- Instead, stored procedures are executed as follows: Exec sp_helpdb;
Syntex
CREATE { PROC | PROCEDURE } [schema_name.]procedure_name [ ; number ]
[ { @parameter [ schema_name.]data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ]
] [ ,...n ]
[ WITH { [ ENCRYPTION ]
, [ RECOMPILE ]
, [ EXECUTE_AS_Clause ]
[ ,...n ] ]
[ FOR REPLICATION ]
SQL_Statements
[ RETURN scalar_expression ]
Basic Stored Procedure Example
CREATE/ALTER PROCEDURE [dbo].[AllContents] AS BEGIN select * from contents END GO
Tags: Stored Procedure