Storage procedure
Create Procedure Procedue_Name [@Parameter Data_Type] [OUTPUT] [with] {recompile | Encryption} assql_statement
Explanation Output: Indicates that this parameter is available .With {recompile | Encryption} Recompile: Re-compiling the encryption of Encryption each time the stored procedure: The content of the stored procedure created will be encrypted
The content of the table book is as follows
Number title price
001 C Language Getting Started with $ 30
002 PowerBuilder Report Development $ 52
Example 1: Query the contents of the table book
Create Proc Query_book as SELECT * FROM BOOK GoExec Query_book
Example 2: Add a record to the table book and query the total amount of all books in this table
Create Proc Insert_book
@ param1 char (10), @ param2 varchar (20), @ param3 money, @ param4 Money Output
WITH ENCRYPTION --------- Encryption
AS
Insert Book (Number, Title, Price) VALUES (@ param1, @ param2, @ param3) Select @ param4 = sum (Price) from book Go
Executive example: declare @total_price Money Exec INSERT_BOOK '003', 'Delphi Development Guide', $ 100, @ Total_Price Print 'total amount is' Convert (varchar, @ Total_Price) Go
Example 3: Established two tables as Product, Order, the table contents are as follows: Product product number Product Name Customer Order 001 Pen 30 002 Brush 50 003 pencil 100 Order Product Number Customer Name Customer Deposit 001 Nanshan District $ 30 002 Luohu District $ 50 003 Baoan District $ 4 Please implement the number as the connection condition, connect two tables into a temporary table, which is only numbered. Product name. Customer name. Deposit. Total amount, total amount = deposit * Order, temporary In the stored procedure
The code is 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 on a. Product number = b. Product number if @@ error = 0 Print 'Good' else print 'Fail' Go
