一，什么是存储过程
存储过程是一组为了完成特定功能的SQL语句集，是利用SQL Server所提供的Transact-SQL语言所编写的程序。经编译后存储在数据库中。存储过程是数据库中一个重要的对象。
通俗的讲它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。
二，存储过程的优点
1.由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比T-SQL语句高。
2.一个存储过程在程序在网络中交互时可以替代大堆的T-SQL语句，所以也能降低网络的通信量，提高通信速率。
3.通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。
三，存储过程的语法
存储过程的一些基本语法：
--------------创建存储过程-----------------
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
--------------调用存储过程-----------------
EXECUTE Procedure_name '' --存储过程如果有参数，后面加参数格式为：@参数名=value，也可直接为参数值value
--------------删除存储过程-----------------
drop procedure procedure_name --在存储过程中能调用另外一个存储过程，而不能删除另外一个存储过程
创建存储过程的参数：
1.procedure_name ：存储过程的名称，在前面加#为局部临时存储过程，加##为全局临时存储过程。
2.; number：是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。
3.@parameter： 存储过程的参数。可以有一个或多个。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2.100 个参数。
使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。有关更多信息，请参见 EXECUTE。
4.data_type：参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。有关 SQL Server 提供的数据类型及其语法的更多信息，请参见数据类型。
说明 对于可以是 cursor 数据类型的输出参数，没有最大数目的限制。
5.VARYING：
default：
OUTPUT
8.RECOMPILE:
9.ENCRYPTION:
10.FOR REPLICATION
11.AS
12.sql_statement
四，创建存储过程
UserAccount
|
UserID
|
UserName
|
PassWord
|
RegisterTime
|
RegisterIP
|
12
|
6
|
6
|
2012-12-31
|
6
|
18
|
5
|
5
|
2013-01-01
|
5
|
19
|
1
|
1
|
2013-01-01
|
1
|
20
|
2
|
2
|
2013-01-01
|
2
|
21
|
3
|
3
|
2013-01-01
|
3
|
22
|
4
|
4
|
2013-01-01
|
4
|
23
|
5
|
5
|
2013-01-01
|
5
|
25
|
7
|
7
|
2013-01-01
|
7
|
26
|
8
|
8
|
2013-01-01
|
8
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
针对上面的表，我使用存储过程对它做一些操作：
1. 只返回单一记录集的存储过程
-------------创建名为GetUserAccount的存储过程----------------
create Procedure GetUserAccount
as
select * from UserAccount
go
-------------执行上面的存储过程----------------
exec
UserAccount 这行代码，结果为整个表的数据。
2.没有输入输出的存储过程
-------------创建名为GetUserAccount的存储过程----------------
create Procedure inUserAccount
as
insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(9,9,'2013-01-02',9)
go
-------------执行上面的存储过程----------------
exec
insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(9,9,'2013-01-02',9) 这行代码。
3.有返回值的存储过程
-------------创建名为GetUserAccount的存储过程----------------
create Procedure inUserAccountRe
as
insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(10,10,'2013-01-02',10)
return @@rowcount
go
-------------执行上面的存储过程----------------
exec
解释：这里的@@rowcount为执行存储过程影响的行数，执行的结果是不仅插入了一条数据，还返回了一个值即 return value =1 ，这个可以在程序中获取，稍后在c#调用存储过程中会有说到。
4.有输入参数和输出参数的存储过程
-------------创建名为GetUserAccount的存储过程----------------
create Procedure GetUserAccountRe
@UserName nchar(20),
@UserID int output
as
if(@UserName>5)
select @UserID=COUNT(*) from UserAccount where UserID>25
else
set @UserID=1000
go
-------------执行上面的存储过程----------------
exec GetUserAccountRe '7',null
@UserName为输入参数，@UserID为输出参数。 运行结果为@userID为COOUT（*）即 =1。
5. 同时具有返回值、输入参数、输出参数的存储过程
-------------创建名为GetUserAccount的存储过程----------------
create Procedure GetUserAccountRe1
@UserName nchar(20),
@UserID int output
as
if(@UserName>5)
select @UserID=COUNT(*) from UserAccount where UserID>25
else
set @UserID=1000
return @@rowcount
go
-------------执行上面的存储过程----------------
exec GetUserAccountRe1 '7',null
@userID为COOUT（*）即 =1，Retun Value=1。
6.同时返回参数和记录集的存储过程
-------------创建名为GetUserAccount的存储过程----------------
create Procedure GetUserAccountRe2
@UserName nchar(20),
@UserID int output
as
if(@UserName>5)
select @UserID=COUNT(*) from UserAccount where UserID>25
else
set @UserID=1000
select * from UserAccount
return @@rowcount
go
-------------执行上面的存储过程----------------
exec GetUserAccountRe2 '7',null
select * from@userID为COOUT（*）即 =1，Retun Value=9。
7.返回多个记录集的存储过程
-------------创建名为GetUserAccount的存储过程----------------
create Procedure GetUserAccountRe3
as
select * from UserAccount
select * from UserAccount where UserID>5
go
-------------执行上面的存储过程----------------
exec
select * fromselect * from UserAccount where UserID>5 。
小结：上面我们创建了各式的存储过程，下面看我们在c#中怎样调用这些存储过程。
五，c#调用存储过程
这里调用的存储过程为上面我写的那些各式各样的存储过程。
public partial class ProcedureTest : System.Web.UI.Page
{
public static string conn = ConfigurationManager.ConnectionStrings["StuRelationDBConnectionString"].ConnectionString;
public SqlConnection con = new SqlConnection(conn);
protected void Page_Load(object sender, EventArgs e)
{
runGetUserAccountRe3();
}
//只返回单一记录集的存储过程GetUserAccount
public void runGetUserAccount()
{
SqlDataAdapter dp = new SqlDataAdapter(common("GetUserAccount"));
DataSet ds = new DataSet();
// 填充dataset
dp.Fill(ds);
rpt.DataSource = ds;
rpt.DataBind();
}
//没有输入输出的存储过程inUserAccount
public void runinUserAccount()
{
con.Open();
Label1.Text = common("inUserAccount").ExecuteNonQuery().ToString();
con.Close();
}
//有返回值的存储过程inUserAccountRe
public void runinUserAccountRe()
{
// 创建参数
SqlCommand cmd = common("inUserAccountRe");
IDataParameter[] parameters = {
new SqlParameter("rval", SqlDbType.Int,4)
};
// 将参数类型设置为 返回值类型
parameters[0].Direction = ParameterDirection.ReturnValue;
// 添加参数
cmd.Parameters.Add(parameters[0]);
con.Open();
// 执行存储过程并返回影响的行数
Label1.Text = cmd.ExecuteNonQuery().ToString();
con.Close();
// 显示影响的行数和返回值
Label1.Text += "-" + parameters[0].Value.ToString();
}
//有输入参数和输出参数的存储过程
public void runGetUserAccountRe()
{
SqlCommand cmd = common("GetUserAccountRe");
// 创建参数
IDataParameter[] parameters = {
new SqlParameter("@UserName", SqlDbType.NChar,20) ,
new SqlParameter("@UserID", SqlDbType.Int) ,
};
// 设置参数类型
parameters[0].Value = "7";
parameters[1].Direction = ParameterDirection.Output; // 设置为输出参数
// 添加参数
cmd.Parameters.Add(parameters[0]);
cmd.Parameters.Add(parameters[1]);
con.Open();
// 执行存储过程并返回影响的行数
Label1.Text = cmd.ExecuteNonQuery().ToString();
con.Close();
// 显示影响的行数和输出参数
Label1.Text += "-" + parameters[1].Value.ToString();
}
//同时具有返回值、输入参数、输出参数的存储过程GetUserAccountRe1
public void runGetUserAccountRe1()
{
SqlCommand cmd = common("GetUserAccountRe1");
// 创建参数
IDataParameter[] parameters = {
new SqlParameter("@UserName", SqlDbType.NChar,20) ,
new SqlParameter("@UserID", SqlDbType.Int) ,
new SqlParameter("rval", SqlDbType.Int,4)
};
// 设置参数类型
parameters[0].Value = "7";
parameters[1].Direction = ParameterDirection.Output; // 设置为输出参数
parameters[2].Direction = ParameterDirection.ReturnValue; //设置为返回值
// 添加参数
cmd.Parameters.Add(parameters[0]);
cmd.Parameters.Add(parameters[1]);
cmd.Parameters.Add(parameters[2]);
con.Open();
// 执行存储过程并返回影响的行数
Label1.Text = cmd.ExecuteNonQuery().ToString();
con.Close();
// 显示影响的行数和输出参数
Label1.Text += "-输出参数为：" + parameters[1].Value.ToString();
Label1.Text += "-返回值为：" + parameters[2].Value.ToString();
}
//同时返回参数和记录集的存储过程GetUserAccountRe2
public void runGetUserAccountRe2()
{
SqlCommand cmd = common("GetUserAccountRe2");
// 创建参数
IDataParameter[] parameters = {
new SqlParameter("@UserName", SqlDbType.NChar,20) ,
new SqlParameter("@UserID", SqlDbType.Int) ,
new SqlParameter("rval", SqlDbType.Int,4)
};
// 设置参数类型
parameters[0].Value = "7";
parameters[1].Direction = ParameterDirection.Output; // 设置为输出参数
parameters[2].Direction = ParameterDirection.ReturnValue; //设置为返回值
// 添加参数
cmd.Parameters.Add(parameters[0]);
cmd.Parameters.Add(parameters[1]);
cmd.Parameters.Add(parameters[2]);
con.Open();
// 执行存储过程并返回影响的行数
Label1.Text = cmd.ExecuteNonQuery().ToString();
DataSet ds = new DataSet();
SqlDataAdapter dt = new SqlDataAdapter(cmd);
dt.Fill(ds);
rpt.DataSource = ds;
rpt.DataBind();
con.Close();
// 显示影响的行数和输出参数
Label1.Text += "-输出参数为：" + parameters[1].Value.ToString();
Label1.Text += "-返回值为：" + parameters[2].Value.ToString();
}
//返回多个记录集的存储过程
public void runGetUserAccountRe3()
{
DataSet ds = new DataSet();
SqlDataAdapter dt = new SqlDataAdapter(common("GetUserAccountRe3"));
dt.Fill(ds);
rpt1.DataSource = ds.Tables[0].DefaultView;
rpt1.DataBind();
rpt2.DataSource = ds.Tables[1].DefaultView;
rpt2.DataBind();
}
public SqlCommand common(string proName)
{
SqlCommand cmd = new SqlCommand();
// 设置sql连接
cmd.Connection = con;
// 如果执行语句
cmd.CommandText = proName;
// 指定执行语句为存储过程
cmd.CommandType = CommandType.StoredProcedure;
return cmd;
}
}
六，将通过几个实例来解析数据库中的sql存储过程，这样就将抽象的事物形象化，比较容易理解。
例1：
create proc proc_stu
@sname varchar(20),
@pwd varchar(20)
as
select * from ren where sname=@sname and pwd=@pwd
go
查看结果：proc_stu 'admin','admin'
例2：
下面的存储过程实现用户验证的功能，如果不成功，返回0，成功则返回1.
CREATE PROCEDURE VALIDATE @USERNAME CHAR(20),@PASSWORD CHAR(20),@LEGAL BIT OUTPUT
AS
IF EXISTS(SELECT * FROM REN WHERE SNAME = @USERNAME AND PWD = @PASSWORD)
SELECT @LEGAL = 1
ELSE
SELECT @LEGAL = 0
在程序中调用该存储过程，并根据@LEGAL参数的值判断用户是否合法。
例3：一个高效的数据分页的存储过程 可以轻松应付百万数据
CREATE PROCEDURE pageTest --用于翻页的测试
--需要把排序字段放在第一列
(
@FirstID nvarchar(20)=null, --当前页面里的第一条记录的排序字段的值
@LastID nvarchar(20)=null, --当前页面里的最后一条记录的排序字段的值
@isNext bit=null, --true 1 :下一页；false 0:上一页
@allCount int output, --返回总记录数
@pageSize int output, --返回一页的记录数
@CurPage int --页号（第几页）0：第一页；-1最后一页。
)
AS
if @CurPage=0--表示第一页
begin
--统计总记录数
select @allCount=count(ProductId) from Product_test
set @pageSize=10
--返回第一页的数据
select top 10
ProductId,
ProductName,
Introduction
from Product_test order by ProductId
end
else if @CurPage=-1--表示最后一页
select * from
(select top 10 ProductId,
ProductName,
Introduction
from Product_test order by ProductId desc ) as aa
order by ProductId
else
begin
if @isNext=1
--翻到下一页
select top 10 ProductId,
ProductName,
Introduction
from Product_test where ProductId > @LastID order by ProductId
else
--翻到上一页
select * from
(select top 10 ProductId,
ProductName,
Introduction
from Product_test where ProductId < @FirstID order by ProductId desc) as bb order by ProductId
end
上文中讲到的这三个例子都是sql存储过程比较典型的例子，希望大家好好学习，都能够学到大家各自需要的东西。
