Instance 1: only the stored procedure of a single record set is returned. The content of the bank deposit table (bankMoney) is as follows: IduserIDSexMoney001Zhangsan male 30002Wangwu male 50003Zhangsan male 40 requirement 1: createproceduresp_query_bankMoneyasselect * fromba
Instance 1: only the stored procedure of a single record set is returned. The contents of the bank deposit table (bankMoney) are as follows: Id userID Sex Money 001 Zhangsan male 30 002 Wangwu male 50 003 Zhangsan male 40 requirement 1: create procedure sp_query_bankMoney as select * from ba
Instance 1: only the stored procedure of a single record set is returned.
The content of the bank deposit table (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
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'
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 %'