SQL Server series: stored Procedures

1. Introduction to Stored Procedures

Stored procedures are code snippets written using T-SQL code. In the stored procedure, you can declare variables, perform conditional judgment statements, and other programming functions. There are three main categories of stored procedures in MS SQL Server 2012: System stored procedures, custom stored procedures, and extended stored procedures.

Advantages of stored procedures:

◊ The stored procedure speeds up the system, and the stored procedures are compiled only at creation time and do not need to be recompiled each time they are executed.

◊ stored procedures can encapsulate complex database operations and streamline operational processes.

◊ Modular programming is possible, and the stored procedure can be called multiple times, providing a unified database access interface and improving the maintainability of the application.

◊ stored procedures can enhance the security of your code.

◊ stored procedures can reduce network traffic, stored procedure code is stored directly in the database, in the client-server communication process, will not generate a lot of T-SQL code traffic.

Disadvantages of stored procedures:

◊ The database porting is inconvenient, the stored procedure relies on the database management system, the operation code encapsulated in the MS SQL Server 2012 stored procedure cannot be ported directly to the other database system.

◊ Object-oriented design is not supported, and the logical business cannot be encapsulated in an object-oriented manner.

◊ Difficult to maintain

◊ Cluster is not supported

1.1 System stored Procedures

A system stored procedure is a stored procedure provided by the MS SQL Server 2012 system itself that can perform various operations as a command. System stored procedures are mainly used to obtain information from the system tables, using the system stored procedures to complete the management of the database server. The system stored procedure is located in the database server and begins with sp_, and the system stored procedure is defined in the system definition and the user-defined database, without having to add a database-qualified name to the stored procedure before it is called.

System stored procedures are created and stored in the system database master.

1.2 Custom stored Procedures

A custom stored procedure is a collection of T-SQL statements written in a user database that users write in a T-SQL statement to implement a particular business need, and the user stored procedure can accept input parameters, return results and information to the client, return output parameters, and so on.

When you create a custom stored procedure, the stored procedure name is preceded by a # #表示创建一个全局的临时存储过程; the stored procedure name is preceded by a # to create a local temporary stored procedure. A local temporary stored procedure can only be used in the session in which it was created, and will be deleted when the session ends. Both of these stored procedures are stored in the tempdb database.

1.3 Extended stored procedures

Extended stored procedures are implemented in DLLs that are executed outside of SQL Server 2012 loop. Extended stored procedure prefix xp_ identity.

2. Creating and executing stored procedures

The syntax format of the CREATE procedure statement:

CREATE{PROC | PROCEDURE}[schema_name.]Procedure_name[; number]     [{@parameter [type_schema_name.]Data_type}[VARYING] [= Default] [Out | OUTPUT | [READONLY]    ] [,... N] [With <procedure_option> [,... n] ][For REPLICATION]  as{[BEGIN]Sql_statement[;] [. .. n] [END] }[;]

The syntax format of the Execute stored procedure:

[{EXEC | EXECUTE}]    {       [@return_status =]{module_name[; number] | @module_name_var }         [[@parameter =]{Value| @variable [OUTPUT]                            | [DEFAULT]                            }        ]      [,... N]      [With <execute_option> [,... n] ]    }[;]

Example:

CREATE PROCEDUREusp_getallproducts as    SELECT [ProductID],[ProductName],[UnitPrice],[UnitsInStock],[CreateDate]     from [dbo].[Product]

EXECUTE Usp_getallproducts

Stored procedure with input parameters:

CREATE PROCEDUREUsp_getbyproductid (@ProductID INT) as    SELECT [ProductID],[ProductName],[UnitPrice],[UnitsInStock],[CreateDate]     from [dbo].[Product]    WHERE [ProductID] = @ProductID

EXECUTE @ProductID = 1

Stored procedure with output parameters:

CREATE PROCEDUREUsp_gettotalrecordsbycategoryid (@CategoryID INT,    @TotalRecords INTOUTPUT) as    SELECT @TotalRecords = COUNT(1) from [dbo].[Product]    WHERE [CategoryID] = @CategoryID

 declare   @TotalProducts  int  execute  Usp_gettotalrecordsbycategoryid  @CategoryID  =  1 ,  @TotalRecords  Span style= "color: #808080;" >=   @TotalProducts   OUTPUT  select   @TotalProducts  

DECLARE @TotalProducts INT EXECUTE 1 @TotalProducts OUTPUT SELECT @TotalProducts

3. Modifying stored Procedures

To modify the stored procedure syntax format:

ALTER{PROC | PROCEDURE}[schema_name.]Procedure_name[; number]     [{@parameter [type_schema_name.]Data_type}[VARYING] [= Default] [Out | OUTPUT] [READONLY]    ] [,... N] [With <procedure_option> [,... n] ][For REPLICATION]  as{[BEGIN]Sql_statement[;] [. .. n] [END] }[;]

4. View stored Procedures

To view the structure of a stored procedure:

EXEC sp_help Usp_gettotalrecordsbycategoryid

To view the stored procedure text:

EXEC sp_helptext Usp_gettotalrecordsbycategoryid

5. Delete a stored procedure

To delete a stored procedure syntax:

DROP PROC | PROCEDURE []procedure[]

Example:

DROP PROCEDURE Usp_gettotalrecordsbycategoryid

SQL Server series: stored Procedures