SQL Server Database Maintenance (UP) _ Stored procedure (procedure)

--Maintenance Database--
--Stored procedure (procedure)--
--Overview：
　　 A stored procedure for SQl serve is a collection of one or more T-SQL statements。 Common program code Snippets are often created as stored procedures that create multiple invocations at once, simplifying the programmer's work and reducing the network traffic that interacts with the server. Stored procedures can contain program statements that perform actions in the database, and also include calls to other procedures. A stored procedure can receive and output parameters and return a value to the program that called it. When a stored procedure is called, it is returned to the program state value that called it to indicate the success of the call or the failure of the call and the reason for the call to fail.
--advantages of using stored procedures：
1)Reduce network traffic。In client-server interaction, each line of code in the T-SQL language is executed using the network, the code is encapsulated as a stored procedure,network send is only available for calls to execute stored procedure statements。
2)Enhanced Security。when a stored procedure is called between the client and the server, only the statements that execute the stored procedure are visible, the user cannot see or access the database objects involved in the stored procedure, and the objects cannot be destroyed, and the stored procedure can be secured using encryption for the stored procedure.
3)Improve programming efficiency。 The encapsulation of common code by the stored procedure takes the writing of de-duplicated code, reduces the inconsistency of the code, and allows the user with permission to access and execute code, which improves the efficiency of code programming.
4)Improve execution efficiency。 By default, only the first time a stored procedure is executed, the stored procedure needs to be compiled, an execution plan is created, and in the future the stored procedure is executed without compiling, saving program processing time and improving execution efficiency. If the data referenced by the stored procedure changes and there is no need to rewrite the stored procedure, the system provides a recompile stored procedure to help the stored procedure execute correctly.
--category: (Stored procedures in SQL Server are divided intouser-defined stored procedures、System Stored ProceduresAndExpand Stored Proceduresthree different types)
1)user-defined stored procedures。the stored procedure encapsulates the function code required by the user, and can be implemented simply by implementing a program code, you can receive user input values (stored procedures with input parameters) by entering parameters, and you can use the output parameters to return the results of the stored procedure execution to the statement that called it (stored procedure with output parameters).
2)System Stored Procedures。A stored procedure that begins with SP_ is a system stored procedure。 is a system-encapsulated program code. For example, sp_helptext represents viewing database object information, and sp_helpindex represents viewing index information in a table.
3)Expand Stored Procedures。A stored procedure that begins with Xp_ is an extension of a stored procedure。 Provided by the system to provide an interface between SQL Server and external programs for the maintenance activities of various systems. For example, Xp_loogininfo indicates that information about Windows users and Windows groups is returned.
--Stored Procedure Management :
-- creation and execution of stored procedures (NOTE: The statement that creates the stored procedure is to exist in the same batch.) )
--stored procedures with no parameters
♦ Create:
CREATE procedure stored procedure nameThe--create procedure command represents the creation of a stored procedure, procedure allows to write only the first four letters proc.
[with encryption]--with encryption is optional and can be encrypted for the creation of stored procedure text.
[WITH RECOMPILE]--with recompile is optional so that the stored procedure does not save the execution plan at execution time and recompile on each execution to prevent overwriting an execution plan that already exists in memory.
as
T-SQL statements

Example: (Create a stored procedure called "P_ Customer Information Table address", which is used to query customer information of "Liaoning Shenyang" in "Customer information sheet" of "Commodity Management Database")
Use Commodity Management database
Go
Select*from Customer Information table where address = ' Shenyang, Liaoning '--check the records to be encapsulated before you can create them

Use Commodity Management database
Go
CREATE proc P_ Customer Information table address--Creating stored procedures
As
Select*from Customer Information table where address = ' Shenyang, Liaoning '

♦ Execution:
The execute stored procedure name --execute represents the execution of a stored procedure and can be abbreviated to exec.

Cases:
Use Commodity Management database
Go
EXEC p_ Customer Information Table _ Address

--Stored procedures with parameters (stored procedures with input parameters and stored procedures with output parameters)
--1) stored procedure with input parameters
♦ Create:
The Create procedure stored procedure name --create Procedure command represents the creation of a stored procedure, procedure allows only the first four letters proc to be written.
@ parameter name data type [(length)] [, ...] --parameter name, should be declared before as
[with encryption] --with encryption is optional and can be encrypted for the creation of stored procedures.
[with recompile] --with recompile is an option, which is that the stored procedure does not save the execution plan at execution time and is recompiled on each execution to prevent overwriting an execution plan that already exists in memory.
As
T-SQL statements

Example: (Create a stored procedure called "P_ Customer Information Table _ Address X", which is used to find customer information for the specified address in the Customer information table of the product Management database)
Use Commodity Management database
Go
CREATE proc P_ Customer Information Table _ address X
@address nvarchar (30)
As
Select*from Customer Information Form where address [email protected]

♦ Execution:
Execute stored procedure name [@ parameter name =] parameter value [, ...] --The data type of the "parameter value" must be compatible with the data type of the assigned parameter

Example: (Execute stored procedure "P_ Customer Information Table _ Address X", find out the address "Shenyang, Liaoning" customer information)
Use Commodity Management database
Go
EXEC p_ Customer Information table _ address x @address = ' Shenyang, Liaoning '
EXEC p_ Customer Information Table _ address X ' Dalian, Liaoning '--Omitting parameter names

--2) stored procedure with output parameters
♦ Create:
The Create procedure stored procedure name --create Procedure command represents the creation of a stored procedure, procedure allows only the first four letters proc to be written.
[@ Parameter name data type [(length)] [, ...,]]
@ Parameter name data type [(length)] output [, ...]
[WITH Encryption]
[WITH RECOMPILE]
As
T-SQL statements
--Allows you to create stored procedures with only input parameters, and also allows you to create stored procedures with both output parameters and input parameters. Output parameters are indicated by adding the OUTPUT keyword after declaring the parameter. The output parameter is typically assigned a value in a T-SQL statement that is encapsulated in a stored procedure.

Example: (Create a stored procedure called "P_ Customer Information Table _ name", which will query the customer information name of customer number 20130001 in the Customer information table and encapsulate the query code that assigns the customer name to an output parameter into the stored procedure)
CREATE proc P_ Customer Information Form _ name
@name nvarchar (5) output
As
Set @name = (select customer name from Customer information table where customer number = ' 20130001 ')
--select @name = Customer name from customer information table where customer number = ' 20130001 '

♦ Execution:
declare @ parameter name data type [(length)] [, ...] --First Use the DECLARE command to declare the parameters involved in the execution, including input and output parameters
[Set @ parameter = value 1] ... --If an input parameter is used, assign a value to the input parameter with the SET statement
Execute stored procedure name [@ parameter [, ...,]] @ parameter output [, ...]

Example: (Execute a stored procedure named "P_ Customer Information Table _ name" to display the customer name with customer number 20130001 in the query "Customer Information sheet")
Use Commodity Management database
Go
declare @name nvarchar (5)
EXEC p_ Customer Information table name @name output
print ' Customer number 20130001 ' Customer Name: ' [email protected]

* * General examples of stored procedures with input parameters, creation and execution of stored procedures with output parameters: (Create a stored procedure called "P_ Customer Information Table _ number _ name", which requires that the customer name that is specified in the Customer information table to be displayed.) and execute the stored procedure to view the results)
CREATE proc P_ Customer Information Form _ number _ name
@num nchar (8), @name nvarchar (5) output-defines an input parameter @num and an output parameter @name, with the data type and range of values consistent with the customer Number field and the Customer Name field in the Customer information table
As
Select @name = Customer name from customer information table where customer number [email protected]

Use Commodity Management database
Go
DECLARE @num nchar (8), @name nvarchar (5)--defines @num and @name two to pass and receive values with the parameters in the stored procedure.
Set @num = ' 20130001 '--assigns a value to the variable @num using the SET command to pass the value to the input parameter @num.
EXEC p_ Customer Information Table _ number _ name @num, @name output--exec execute the stored procedure sequentially to bring two variables into the stored procedure.
print ' customer number ' [email protected]+ ' Customer's name is: ' [email protected] The--print statement uses a string join operation to output detailed results.

--Viewing stored procedures
Note: You can use system stored proceduressp_helptext、sp_helpAndsp_dependsView the creation information and create text for the stored procedure.
exec System Stored procedure user-defined stored procedure name
Descriptionsp_helptextThat shows the stored procedure being viewed.Creating text Messages, the created text information for the stored procedure that was encrypted when it was created cannot be viewed.
sp_helpDisplays the view of the stored procedure that was viewedowner、type、creation TimeAndwhich parameters are includedand other information.
sp_dependsShow the stored procedure being viewedThe associated data table and field information。

Cases:
Use Commodity Management database
Go
EXEC sp_depends p_ Customer Information Form _ name
EXEC sp_help p_ Customer Information Form _ name
EXEC sp_helptext p_ Customer Information Form _ name

-- modifying stored procedures
Note: During the management database process, you can modify the user-defined stored procedures that you have created as needed. For stored procedures that have been encrypted, you cannot modify their contents by using the manager, and users with permissions can modify their creation in the T-SQL language while removing the cryptographic attributes, and when essentially deleting the original encrypted stored procedure, recreate a new stored procedure and use it with caution.
alter procedure stored procedure name ---The ALTER command represents the modification, "stored procedure name" must be a user-defined stored procedure that already exists. Procedure allows to write only the first four letters of the Proc.
[@ Parameter name data type [(length)] [, ...,]] @ parameter name data type [(length)] output [, ...]
[WITH Encryption]
[WITH RECOMPILE]
As
T-SQL statements

Example: (Modify the function of a stored procedure named "P_ Customer Information Table _ name" to find customer information with customer number 20130003 and pass the result to a parameter)
Use Commodity Management database
ALTER proc P_ Customer Information Form _ name
@name nvarchar (5) output
As
Set @name = (select customer name from Customer information table where customer number = ' 20130003 ')

-- Delete stored procedures
Note: For stored procedures that you no longer use, you can save disk space by removing them. Deleting a stored procedure does not affect database objects such as the data tables it involves, but the statement that invokes it will produce an error when it is used again.
The drop procedure stored procedure name --drop command represents the Delete command, you can delete the overweight and unencrypted stored procedures , and delete without confirming the deletion of the prompt, you need to use caution . Procedure allows to write only the first four letters of the Proc.

Example: (delete an encrypted stored procedure named "P_ Inbound Information Table _ Product number")
Use Commodity Management database
Go
drop proc P_ Stock Information Table _ Item number

-- recompile stored procedures
Note: Because the stored procedure is executed once, the compilation process is logged into memory, and when the same stored procedure is executed again, it does not need to be compiled and executed directly. As the user operates on the system, the data in the database can change at any time, and if the change involves a data table associated with the stored procedure, the stored procedure needs to be recompiled to get the correct execution result. There are three ways to recompile a stored procedure .
1) Recompile while creating the stored procedure
Create PROCEDURE Stored Procedure name
The WITH recompile --with recompile command causes the stored procedure to not save the execution plan at execution time and recompile on each execution to prevent overwriting an execution plan that already exists in memory.
As
T-SQL statements

2) Recompile while executing the stored procedure
exec stored procedure name with recompile --using the WITH RECOMPILE statement while executing the stored procedure allows the stored procedure to be recompiled at execution time.

3) preset stored procedure recompile using Command mode
exec sp_recompile Stored procedure name --You can recompile a stored procedure preset that has already been created, and recompile the stored procedure the next time the stored procedure executes, from the end of the preset statement execution.

Example: (Recompile the stored procedure "P_ Customer Information Table _ Name" preset using system stored procedure sp_recompile)
Use Commodity Management database
Go
EXEC sp_recompile ' p_ Customer Information Form _ name '

Maintenance of SQL Server Database (UP) _ Stored procedure (procedure)