MSSQL Stored Procedure

 

Instance operations teach you how to use SQL Server Stored Procedures

First, we will introduce what a stored procedure is: a stored procedure stores common or complex tasks with a specified name by writing SQL statements in advance, in addition, when such statements are stored in the database and different SQL statements can be executed according to the conditions, the database will be called to provide services with the same functions as the predefined stored procedure, you only need to call execute to automatically complete the command. Let's take a look at the Stored Procedure syntax. Create proc [edure] procedure_name [; number] [{@ Parameter data_type} [Varying] [= default] [Output] ] [,... N] [ {Recompile | encryption | recompile, encryption}] [For replication] As SQL _statement [... n] Parameters: 　　Procedure_name Name of the new stored procedure. The process name must comply with the identifier rules and must be unique to the database and its owner. To create a local temporary process, you can add a identifier (# procedure_name) before procedure_name. To create a global temporary process, you can add two identifier (# procedure_name) before procedure_name ). The complete name (including # Or #) cannot exceed 128 characters. The name of the specified process owner is optional. 　　; Number Is an optional integer used to group processes with the same name, so that the same group of processes can be removed with a drop procedure statement. For example, the process used by an application named orders can be named orderproc; 1, orderproc; 2, and so on. The drop procedure orderproc statement removes the entire group. If the name contains a bound identifier, the number should not be included in the identifier and the appropriate delimiters should be used before and after procedure_name. 　　@ Parameter Parameters in the process. One or more parameters can be declared in the create procedure statement. You must provide the value of each declared parameter during execution (unless the default value of this parameter is defined ). A stored procedure can have a maximum of 2100 parameters. Use the @ symbol as the first character to specify the parameter name. The parameter name must comply with the identifier rules. Each process parameter is only used for this process. The same parameter name can be used in other processes. By default, parameters can only replace constants, but cannot replace the names of table names, column names, or other database objects. 　　Data_type The data type of the parameter. All data types (including text, ntext, and image) can be used as parameters for stored procedures. However, the cursor data type can only be used for output parameters. If the specified data type is cursor, the varying and output keywords must also be specified. Note: There is no limit on the maximum number of output parameters that can be cursor data types. Varying Specify the result set supported by the output parameter (dynamically constructed by the stored procedure, and the content can be changed ). Only applicable to cursor parameters. 　　Default The default value of the parameter. If the default value is defined, the process can be executed without specifying the value of this parameter. The default value must be a constant or null. If the like keyword is used for this parameter, the default value can contain wildcards (%, _, [], and [^]). 　　Output Indicates that the parameter is a return parameter. The value of this option can be returned to Exec [ute]. The output parameter can be used to return information to the call process. The text, ntext, and image parameters can be used as output parameters. The output parameter using the output keyword can be a placeholder cursor. 　　N It indicates that up to 2100 parameter placeholders can be specified. {Recompile | encryption | recompile, encryption} Recompile indicates that SQL server does not cache the plan of the process, and the process will be re-compiled at runtime. Use the recompile option when you use an atypical or temporary value instead of overwriting the execution plan cached in the memory. Encryption indicates the entries in the SQL Server encrypted syscomments table that contain the create procedure statement text. Encryption prevents the process from being published as part of SQL Server replication. Note: During the upgrade, SQL server uses the encryption notes stored in syscomments to recreate the encryption process. 　　For Replication The storage process created for replication cannot be executed on the subscription server .. Stored Procedures created using the for replication option can be used for filtering stored procedures and can only be executed during replication. This option cannot be used with the with recompile option. 　　As Specifies the operation to be performed in the process. 　　SQL _statement Any number and types of transact-SQL statements to be included in the process. But there are some restrictions. 　　N Yes indicates that this process can contain placeholders for multiple Transact-SQL statements. 　　Note The maximum size of stored procedures is 128 MB. What are the advantages of stored procedures? 1. the stored procedure can be compiled only when it is created. You do not need to re-compile the stored procedure every time you execute it. However, the SQL statement we usually use is compiled every time you execute it, therefore, using stored procedures can speed up database execution. 2. complex business logic and database operations are often encountered. In this case, SP is used to encapsulate database operations. When performing complex operations on the database (for example, performing update, insert, query, and delete operations on multiple tables ), this complex operation can be encapsulated in a stored procedure and used together with the transaction processing provided by the database. It can greatly improve database usage efficiency and reduce program execution time, which is very important in database operations with large data volumes. In terms of code, the separation of SQL statements and program code statements can improve the readability of program code. 3. You can set parameters for stored procedures. You can reuse the same stored procedure based on different input parameters to improve the code optimization rate and readability. 4. High security. You can set the type of stored procedure that only a user can use for a specified stored procedure: (1) system stored procedure: starts with SP _. It is used to set the system and obtain information. related management work. For example, sp_help is used to obtain information about the specified object. (2) The extended stored procedure starts with XP _ and is used to call the functions provided by the operating system. Exec master .. xp_mongoshell 'Ping 10.8.16.1' (3) User-Defined stored procedure, which is a common format of Stored Procedure Template: Create procedure procedue_name [@ parameter data_type] [Output] [With] {recompile | encryption} As SQL _statement Explanation: Output: this parameter can be returned. With {recompile | encryption} recompile: indicates that the stored procedure is re-compiled every time it is executed. encryption: the content of the created stored procedure is encrypted. Instance 1: only the stored procedure of a single record set is returned. The table of bank deposits (bankmoney) is as follows: ID Userid Sex Money 001 Zhangsan Male 30 002 Wangwu Male 50 003 Zhangsan Male 40 Requirement 1: query the stored procedure of bankmoney Create procedure sp_query_bankmoney As Select * From bankmoney Go Exec sp_query_bankmoney Note * you only need to replace the SQL statement with the name of the stored procedure during usage, which is convenient! 　　Instance 2 (passing parameters to the stored procedure ): Add a record to the bankmoney table and query the total amount of all deposits in the table userid = zhangsan. Create proc insert_bank @ param1 char (10), @ param2 varchar (20), @ param3 varchar (20), @ param4 int, @ param5 int output With encryption --------- Encryption As Insert bankmoney (ID, userid, sex, money) Values (@ param1, @ param2, @ param3, @ param4) Select @ param5 = sum (money) from bankmoney where userid = 'hangsan' Go You can run this stored procedure in the SQL Server Query analyzer: Declare @ total_price int Exec insert_bank '004 ', 'hangsan', 'mal', 100, @ total_price output Print 'total balance is '+ convert (varchar, @ total_price) Go Here, I will refer to the three types of return values of the stored procedure (so that the users who are reading this example do not have to check the syntax ): 1. Return an integer with return 2. Return parameters in output format 3. recordset Differences between return values: Both output and return can be received using variables in a batch program, while recordset is passed back to the client that executes the batch. Example 3: simple process with complex select statements The following stored procedure returns all authors (names provided), published books, and publishers from the join of the four tables. This stored procedure does not use any parameters. Use pubs If exists (Select name from sysobjects Where name = 'au _ info_all 'and type = 'P ') Drop procedure au_info_all Go Create procedure au_info_all As Select au_lname, au_fname, title, pub_name From authors a inner join titleauthor Ta On a. au_id = TA. au_id inner join titles t On T. title_id = TA. title_id inner join publishers P On T. pub_id = P. pub_id Go The au_info_all stored procedure can be executed in the following ways: Execute au_info_all -- Or Exec au_info_all If the process is the first statement in batch processing, you can use: Au_info_all 　　Example 4: simple process with Parameters Create procedure au_info @ Lastname varchar (40 ), @ Firstname varchar (20) As Select au_lname, au_fname, title, pub_name From authors a inner join titleauthor Ta On a. au_id = TA. au_id inner join titles t On T. title_id = TA. title_id inner join publishers P On T. pub_id = P. pub_id Where au_fname = @ firstname And au_lname = @ lastname Go The au_info stored procedure can be executed in the following ways: Execute au_info 'dull ', 'ann' -- Or Execute au_info @ lastname = 'dull ', @ firstname = 'ann' -- Or Execute au_info @ firstname = 'ann ', @ lastname = 'dull' -- Or Exec au_info 'dull ', 'ann' -- Or Exec au_info @ lastname = 'dull ', @ firstname = 'ann' -- Or Exec au_info @ firstname = 'ann ', @ lastname = 'dull' If the process is the first statement in batch processing, you can use: Au_info 'dull', 'ann' -- Or Au_info @ lastname = 'dull ', @ firstname = 'ann' -- Or Au_info @ firstname = 'ann ', @ lastname = 'dull' Example 5: simple process with wildcard Parameters Create procedure au_info2 @ Lastname varchar (30) = 'd % ', @ Firstname varchar (18) = '%' As Select au_lname, au_fname, title, pub_name From authors a inner join titleauthor Ta On a. au_id = TA. au_id inner join titles t On T. title_id = TA. title_id inner join publishers P On T. pub_id = P. pub_id Where au_fname like @ firstname And au_lname like @ lastname Go The au_info2 stored procedure can be executed in multiple combinations. Only some combinations are listed below: Execute au_info2 -- Or Execute au_info2 'wh %' -- Or Execute au_info2 @ firstname = 'a %' -- Or Execute au_info2 '[Ck] ARS [OE] N' -- Or Execute au_info2 'hunter', 'sheryl' -- Or Execute au_info2 'H % ','s %' = 'Proc2'