How to create a stored procedure

This article focuses onCreate a table using a stored procedureAndHow to create a stored procedureAnd how to create an article with a stored procedure as the topic

The table book content is as follows:

Title price

001 C language entry $30

002 PowerBuilder report development $52

Example 1: query the stored procedure of the table Book content

Reference content is as follows:

Create proc query_book

 

As

 

Select * from book

 

Go

Exec query_book

 

Example 2: Add a record to the table book and query the total amount of all books in the table

 

Reference content is as follows:

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
 

Example:

Reference content is as follows:
Declare @ total_price money
Exec insert_book '003 ', 'Delphi control development Guide', $100, @ total_price
Print 'total amount is '+ convert (varchar, @ total_price)
Go

Three types of stored procedure return values:
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: There are two tables: Product and Order. The table content is as follows:

Reference content is as follows:
Product
Product No. Product name customer order quantity
001 pen 30
002 brush 50
003 pencil 100
Order
Product No. Customer name customer deposit
001 Nanshan District $30
002 Luohu District $50
003 Baoan District $4

Connect two tables into a temporary table by serial number. This table only contains serial numbers, product names, customer names, deposits, and total amount,
Total amount = deposit * number of orders. Temporary tables are stored in the stored procedure.

The code is as follows:

Reference content is as follows:
Create proc temp_sale
As
Select a. Product no., a. Product name, B. Customer name, B. Customer deposit, a. Number of customer orders * B. Total amount of customer deposits
Into # temptable from Product a inner join order B on a. Product No. = B. Product No.
If @ error = 0
Print 'good'
Else
Print 'fail'
Go