Simple SQL stored procedure instance, storage process combat

Instance 1: A stored procedure that returns only a single recordset.

The contents of the Bank deposit form (Bankmoney) are as follows

Id	userid	Sex	money< /p>
001	Zhangsan	male
002	Wangwu	Male	,
003	Zhangsan	Male	$

Requirement 1: A stored procedure that queries the contents of a table Bankmoney

CREATE PROCEDURE Sp_query_bankmoney
As
SELECT * FROM Bankmoney
Go
EXEC Sp_query_bankmoney

Note * In the use of the process only need to replace the SQL statements in the stored procedure name, it can be very convenient!

Instance 2 (passing parameters to stored procedures):

Add a record to the table Bankmoney and query the total amount of all deposits userid= zhangsan in this table.

Create proc Insert_bank @param1 char (@param2 varchar), @param3 varchar, @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= ' Zhangsan '
Go
The method for executing the stored procedure in SQL Server Query Analyzer is:
DECLARE @total_price int
EXEC insert_bank ' 004 ', ' Zhangsan ', ' Male ', MB, @total_price output
print ' Total balance is ' +convert (varchar, @total_price)
Go

Here's a little bit of a second. 3 return values for the stored procedure (convenient for those who are looking at this example no longer have to look at the grammatical content):

1. Returns an integer with return

2. Returns parameters in output format

3.Recordset

The difference between return values:

Both output and return can be received in a batch program and the recordset is returned to the client of the execution batch.

Example 3: Using a simple procedure with a complex SELECT statement

The following stored procedure returns all authors (names), published books, and publishers from a four-table join. The 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

Au_info_all stored procedures can be performed in the following ways:

EXECUTE Au_info_all

Example 4: Using a simple procedure 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

Au_info stored procedures can be performed 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: Using a simple procedure with wildcard parameters

CREATE PROCEDURE Au_info2
@lastname varchar = ' 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

Au_info2 stored procedures can be executed in a variety of combinations. Only some of the 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% '