A simple instance of a stored procedure and a simple instance of a stored procedure

A simple instance of a stored procedure and a simple instance of a stored procedure

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 %'