存储过程

–创建存储过程的语法
if exists(select * from sysobjects where name=‘sp_selectStudent’)
drop procedure sp_selectStudent
go
create procedure sp_selectStudent
–定义参数
@gradeid int
–可以定义输出参数,也有返回值,只有返回整型 return 0
as
select * from Student where XSGrade=@gradeid
–写语句
go
–调用存储过程
exec sp_selectStudent 10
----增加年级的存储过程
if exists(select * from sysobjects where name=‘sp_addGrade’)
drop procedure sp_addGrade
go
create procedure sp_addGrade --定义存储过程
@njname varchar(20) --定义参数 各个参数,隔开
as
insert into Grade(NJName) values(@njname)
go
–调用存储过程
exec sp_addGrade ‘S1’
–删除年级的存储过程，如果有学生，先删学生，再删年级
–[存储过程封装业务功能，使用事务]
if exists(select * from sysobjects where name=‘sp_deleteGrade’)
drop procedure sp_deleteGrade
go
create procedure sp_deleteGrade
@gradeid int --要删除的年级id
as
begin transaction --1开启事务
declare @err int --2.错误变量
set @err=0
–一个语句一个语句执行
delete from Student where XSGrade=@gradeid
set @err=@err+@@ERROR --错误累加
–
delete from Grade where NJId=@gradeid
set @err=@err+@@ERROR
–
if(@err>0)
begin
rollback transaction–回退事务
end
else
begin
commit transaction --提交事务
end
go

----------------ADO.net调用存储过程
#region 调用删除年级的存储过程[调用增删改的存储过程方式]
public void DelteGradePro(int gradeid)
{
//1.创建连接对象
string Connstring = “Data Source=.;Initial Catalog=y2Net22DB;Integrated Security=True”;
SqlConnection sqlconn = new SqlConnection(Connstring);
//2.执行的sql语句或者存储过程名称
string sql = “sp_deleteGrade”;//存储过程名
SqlCommand sqlcomm = new SqlCommand(sql, sqlconn);
// 存储过程一定加上操作类型属性
sqlcomm.CommandType = CommandType.StoredProcedure;//说明是存储过程还是sql语句
// 给存储过程的输入参数赋值
sqlcomm.Parameters.Add("@gradeid", gradeid);
//3.打开连接
sqlconn.Open();
//4.执行操作
sqlcomm.ExecuteNonQuery();
//5.关闭
sqlconn.Close();
}
#endregion