ADO 与MSS SQL Server 连接应用、触发器

2.
create proc usp_login
@uname varchar(50),
@password varchar(50),
@result bit output
as
 begin
  //declare @count int
  set @count=(select count(*)from T_Users where FuserName=@uname and Fpassword=@password)
if @count>0
  begin
   set @result=1
    end
else
 begin
   set @result=0
    end
  end
declare @count int
exec usp_login

----向特定表中插入记录的存储过程
use UserDB1
select * from T_Users

create proc usp_Registor
@name varchar(50),
@pwd varchar(50),
@email varchar(50)
as
begin
   insert into T_Users(FuserName,Fpassword,Femail) valuse(@name,@pwd,@email)
end

exec usp_Registor 'zhanglan','zhanglan','zl@163.com'
--删除存储过程特定表的一条记录
use school
select * from Student
create proc usp_Student_delete
@sId int
as
 begin
  delete from Student where siId=@sId
 end

exec usp_Student_delete @sId=14
--修改（更新）
select * from Class
 insert into Class valuse('Net','优秀班集体')
 insert into Class valuse('Java','准优秀班集体')

create procedure usp_Class_update
@Cid int,
@Cname varchar(50)='3G',
@cDes varchar(50)='快班'
as
 begin
  update Class set cName=@Cname,cDescription=@cDes where cId=@Cid
 end

exec  usp_Class_update 10,'3G','准优秀班集体'

--实现一个针对特定表的分页得过程
select * from
--分页
select * from
(select *, row_number() over(order by Fid) as rowIndex
from MyStudent) as tb1
where tb1.rowIndex between(5*2+1)and 5*3
---------------
create proc usp_MyStudent_GetdataByPageIndex
@pageSize int=10,
@pageIndex int
as
begin
select * from
(select *, row_number() over(order by Fid) as rowIndex
from MyStudent) as tb1
where tb1.rowIndex between(@pageSize*(@pageIndex-1)+1)and @pageIndex*@pageSize
end

exec usp_MyStudent_GetdataByPageIndex 6,20

--触发器：是一种特殊类型的存储过程，主要是通过事件进行触发而被执行
作用：自动化操作

DML 触发器