存储过程，触发器，等等。。。_.NET_编程开发_程序员俱乐部

存储过程

if (object_id('proc_find_stu', 'P') is not null)
    drop proc proc_find_stu
go

create proc proc_find_stu(@startId int, @endId int,@outID int output)
as

    select * from Studentss where id between @startId and @endId
    
   set  @outID=(select COUNT(1) from Studentss where id between @startId and @endId)
 


  declare @ss int=0;
  exec proc_find_stu 1,6, @ss out
  select @ss

触发器---修改触发器

create trigger class_stu
on goods
for update
as
   declare @oldnumber  int,@newnumber int,@id int ;
 
      select @oldnumber=  number  from deleted;--旧数量
       select @id= goodsid  from deleted    ;--id   
 
    select @newnumber = number from inserted;--新数量

    update cangku set number=@oldnumber-(@oldnumber-@newnumber) where goodsid=@id;

    drop trigger class_stu

    update goods set  number=number-1 where goodsid=1;

    select * from goods g inner join cangku c on g.goodsid=c.goodsid

视图

use Students
select * from Studentss
--创建视图
if (exists (select * from sys.objects where name = 'v_stu'))
    drop view v_stu

create view v_stu  as select Id, StuName, StuClass, TeamName,Bishi,Jineng from Studentss;

select * from v_stu


--修改视图
alter view v_stu as  select id, name, sex from student;
alter view v_stu(编号, 名称,班级, 小组名称,笔试,机试)  as select Id, StuName, StuClass, TeamName,Bishi,Jineng from Studentss

select * from v_stu where 班级='1510A';

select * from information_schema.views;