存储过程

存储过程的概念：

　　　　存储过程(Stored Procedure)是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程的优点：

存储过程与存储在客户本地的T-SQL程序相比，具有如下优点：

u       模块化的程序设计：存储过程经过一次创建之后，可以被无数次调用，所以增加代码的重用性和共享性，提高应用程序的开发的质量和效率。另外，用户可以独立于应用程序而对存储过程进行修改，不影响源程序。

u       执行速度快：存储过程在创建时就经过了语法检查和性能优化，因此在执行时不必重复这些步骤。存储过程在经过第一次执行后，SQL Server为其产生查询计划并将其保存在内存中，以后在调用存储过程时就不必再进行编译和优化，所以执行速度快。在有大量批处理的T-SQL要重复执行的时候，使用存储过程可以极大地提高运行效率。

u       减少网络流量：一个操作可能需要上百行T-SQL语句组成，当将其包含在存储过程中后，可以通过一条调用语句来执行它。这样可以避免这上百条语句通过网络传输，从而减少了网络的负荷。

u       保证系统安全性：可以设置用户通过存储过程来对某些关键数据进行访问，但不允许用户直接使用T-SQL或企业管理器来对数据进行访问。

1.不带参数的存储过程：

/*不带参数的存储过程*/ create procedure SelectEmployees as select EmployeeID,FirstName,LastName from Employees
--执行 exec SelectEmployees

SqlConnection con = new SqlConnection(""); SqlCommand cmd = new SqlCommand("SelectEmployees", con); //设置SqlCommand对命令类别为存储过程 cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet ds=new DataSet(); sda.Fill(ds, "emp");

2.带输入参数的存储过程：

Code 
/*创建带输入参数的存储过程，向类别表中添加数据*/ 
create procedure InsertCategory 
( 
@CategoryName nvarchar(15), 
@Description ntext 
) 
as 
insert into Categories (CategoryName,Description) values (@CategoryName,@Description) 
 
--调用带输入参数的存储过程 
exec InsertCategory '我的类别','描述' 

Code 
public bool InsertData(string categoryName, string description) 
{ 
SqlConnection con = new SqlConnection(""); 
SqlCommand cmd = new SqlCommand("InsertCategory", con); 
cmd.CommandType = CommandType.StoredProcedure; 
 
/*设置存储过程所需要的参数，并指定参数的值*/ 
cmd.Parameters.AddWithValue("@CategoryName", categoryName); 
cmd.Parameters.AddWithValue("@Description", description); 
 
con.Open(); 
int i = cmd.ExecuteNonQuery(); 
con.Close(); 
 
return i == 1; 
} 

   3.带输入参数和输出参数的存储过程

Code 
--调用带输入和输出参数的存储过程 
create procedure MathAdd 
( 
@n1 smallint, 
@n2 smallint, 
@result smallint output 
) 
as 
set @result = @n1 + @n2 
 
declare @answer smallint 
 
exec MathAdd 20,24,@answer output 
 
select @answer 

用存储过程来实现登陆：

Code 
/*登陆表*/ 
create table Users 
( 
ID uniqueidentifier default newid(), 
username varchar(50) primary key, 
password varchar(50) not null, 
lastauth datetime default getdate() 
) 
 
create procedure Login 
( 
@username varchar(50), 
@password varchar(50), 
@message tinyint output --0-用户名不存在,1登陆成功,2密码错误 
) 
as 
declare @UName varchar(50),@Pwd varchar(50) 
 
select @UName = username,@Pwd = [password] from Users where username = @username 
 
if(@UName is not null) 
begin 
if(@Pwd = @password) 
begin 
set @message = 1 
update users set lastauth = getdate() where username = @username 
end 
else 
begin 
set @message = 2 
end 
end 
else 
begin 
set @message = 0 
end 
 
--调用存储过程 
declare @msg tinyint  
exec Login 'sa','admin',@msg output 
select @msg 

Code 
private byte Login(string username, string password) 
{ 
SqlConnection con = new SqlConnection(""); 
SqlCommand cmd = new SqlCommand("Login", con); 
cmd.CommandType = CommandType.StoredProcedure; 
 
byte msg = 4;//初始化值为取不到的一个值 
cmd.Parameters.AddWithValue("@username",username); 
cmd.Parameters.AddWithValue("@password", password); 
 
cmd.Parameters.Add("@message", SqlDbType.TinyInt); 
cmd.Parameters["@message"].Direction = ParameterDirection.Output; 
 
con.Open(); 
cmd.ExecuteNonQuery(); 
//获取输出参数的值 
object o = cmd.Parameters["@message"].Value; 
msg = (byte)o;//转化为byte类型(所谓的拆箱) 
con.Close(); 
 
return msg; 
} 

4.带返回值的存储过程

Code 
 
--存储过程的返回值(在有标识列的表中插入数据后获取标识列的值) 
create procedure InsertEmployee 
( 
@FirstName nvarchar(10), 
@LastName nvarchar(20) 
) 
as 
insert into Employees (LastName,FirstName) values (@LastName,@FirstName) 
return @@identity 
 
 
--调用举例 
declare @ret int 
 
execute @ret = InsertEmployee 'T','D' 
 
select @ret

Code 
private int InsertWithReturnValue(string firstName, string lastName) 
{ 
SqlConnection con = new SqlConnection(""); 
SqlCommand cmd = new SqlCommand("InsertEmployee", con); 
cmd.CommandType = CommandType.StoredProcedure; 
 
cmd.Parameters.AddWithValue("@FirstName", firstName); 
cmd.Parameters.AddWithValue("@LastName", lastName); 
 
cmd.Parameters.Add("@ret", SqlDbType.Int); 
cmd.Parameters["@ret"].Direction = ParameterDirection.ReturnValue; 
 
con.Open(); 
cmd.ExecuteNonQuery(); 
object o = cmd.Parameters["@ret"].Value; 
con.Close(); 
 
return (int)o; 
}