Definition: Write common or complex work, pre-use SQL statements and store it with a specified name, then call the database to provide the same service as the defined stored procedure, simply call Execute, You can automate the command. Speaking here, some may ask: This is said that the stored procedure is a bunch of SQL statements? Why should Microsoft add this technology? So what is the difference between the stored procedure with the general SQL statement? Advantages of the stored procedure: 1. The stored procedure is only compiled when it is created, and each execution stored procedure does not need to be re-needed. Compile, and the general SQL statement is compiled once every time, it can improve the database execution speed using the stored procedure. 2. When performing complex operations for the database (such as Update, Insert, Query, Delete to multiple tables), this complex operation stored procedure can be encapsulated together with the transaction processing provided by the database.
3. The stored procedure can reuse, reduce the workload of the database developer 4. High security, which can be set to have only one user with the type of storage procedure for the use of the stored procedure: 1. System stored procedure: SP_ starts to perform system settings. Take information. Relevant management work, such as sp_help is related information to obtain the specified object 2. Extended the stored procedure to start with XP_, to call the function provided by the operating system EXEC MASTER .. xp_cmdshell 'ping 10.8.16.1' 3. User-defined stored procedure, this is what we finishes on the stored process common format Create Procedure Procedue_name [@Parameter Data_Type] [OUTPUT] [with] {recompile | encryption} as sql_statement explanation : Output: Indicates that this parameter is the available with {recompile | encomtion: Re-compiling the enclapping of Encryption each time the stored procedure: The content of the stored procedure created is encrypted as: Table BOOK content is as follows Number Title Price 001 C Language Getting Stated $ 30 002 PowerBuilder Report Develop $ 52 Instance 1: Squiry Table Book content stored procedure Create Proc Query_book asse SELECT * FROM BOOK Go EXEC Query_book Instance 2: Add a record to table book, and query this Total amount of all books Create Proc insert_book @ param1 char (10), @ Par Am2 varchar (20), @ param3 money, @ param4 Money Output with encryption --------- Encrypted AS INSERT BOOK (No., Title, Price) VALUES (@ param1, @ param2, @ param3) SELECT @ Param4 = sum Executive example: declare @total_price Money Exec INSERT_BOOK '003', 'DELPHI Control Development Guide', $ 100, @ Total_Price Print 'total amount is' Convert (varchar, @
Total_Price) The go stored procedure 3 types of transit: 1. Reallographic integer in RETURN 2. Transfer parameters in OUTPUT Format 3. Difference toRecordSet Passage: Output and Return can be received in batches in batches. And RecordSet is sent back to the client of the execution batch 3: There are two tables as Product, Order, the table content is as follows: Product Product No. Product Name Customer Order 001 Pen 30 002 Brush 50 003 Pencil 100 ORDER Product Number Customer Deposit 001 Nanshan District $ 30 002 Luohu District $ 50 003 Baoan District $ 4 Please implement the number as a connection condition, connect the two tables into a temporary table, which is only numbered. Product name. Customer name. Deposit. Total The amount, total amount = deposit * Order, the temporary table is placed in the stored procedure as follows: create proc temp_sale as select a. Product number, a. Product name, b. Customer name, b. Customer deposit, a. Customer order * B. Customer deposit AS total amount INTO #temptable from product a inner join order b. Product number = b. Product number IF @@ error = 0 Print 'good' else & n bsp; print 'fail' Go store process introduction First, introduce what is that the stored procedure stored procedure is the program written by the TRANACT-SQL language provided by SQL Server. The TRANACT-SQL language is SQL Server provides a language designed to design a database application, which is the primary programming interface between the application and the SQL Server database. It is better than the Informix-4GL language that Pro-SQL and Informix in the Oracle database system. Such languages provide the following functions, allowing users to design programs that meet the reference requirements: 1), variables 2), ANSI-compatible SQL commands (such as SELECT, UPDATE ....) 3), general flow control command (if ... Else ..., while ....) 4), internal functions
Second, the writing of the stored procedure
Create Procedure [owner.] Store process name [; program number] [(parameter # 1, ... parameter # 1024)] [with {recompile | encryption | Recompile, Encryption}] [for replication] AS line
The stored procedure name cannot exceed 128 words. Set up to 1024 parameters during each store (SQL Server 7.0 or higher), the method of use of the parameters is as follows:
@ 参数 Name Data Type [Varying] [= Normal] [OUTPUT]
There is a "@" symbol before each parameter name. The parameters of each stored procedure are only internal to the program, and the type of data supported by other SQL Server is available in addition to Image. [= Obredity] It is equivalent to setting the default value of a field when we set up a database, here is the default value for this parameter. [OUTPUT] is used to specify that the parameter is both input and output values, that is, when the stored procedure is called, if the specified parameter value is the parameters we need to enter, it also needs to be output in the results. If the item must be OUTPUT, if only the output parameter is used, you can use Cursor, and when using this parameter, you must specify the two statements of Varying and Output. Example: CREATE Procedure Order_tot_amt @o_id int, @p_tot = sum (unitprice * quantity) from OrderDetails Where Ordered = @ o_id
Example: This example is a simple stored procedure order_tot_amt, which calculates the total sales of the order (Unitprice *) according to the order ID number (@o_id) entered by the user input. Quantity)] This amount outputs a program that calls this stored procedure by @P_tot this parameter.
Third, perform a stored procedure in SQL Server
In the Query Analyzer of SQL Server, enter the following code: declare @tot_amt int execute order_tot_amt 1, @ Tot_amt Output SELECT @tot_amt
The above code is the stored procedure for executing the order_tot_amt to calculate the order sales amount of 1, we define @tot_amt as the output parameters, used to undertake the results we want
