sqlserver储存过程简单实例

//最简单的存储过程
create proc proc_get_student
as 
	select * from oNotice
go	
//执行存储过程
exec proc_get_student
//删除存储过程
drop proc proc_get_student
//创建带参数的存储过程
create proc testProc 
@xx int,
@oo varchar(20)
as 
	select  * from oNotice where  IsDeleted = 0 and ID = @xx and MemoInfo = @oo
go
drop proc testProc

//创建带参数的存储过程，记得如果是top 的情况下，要在top后面的参数前后加上括号
create proc testProc 
@xx int,
@oo int
as 
	select  top (@xx) * from oNotice where  IsDeleted = 0 and ID not in
	(select top (@oo) ID from oNotice where IsDeleted = 0)
go
exec  testProc 1,10

drop proc xxx
//创建带输出参数的存储过程
create proc xxx 
@xx int,
@oo varchar(20) out
as 
	select @oo = MemoInfo from oNotice where ID = @xx
go
declare @oo varchar(20)
exec xxx 1,@oo out
print @oo
//创建存储过程 用来获取刚刚添加进去的数据的ID 
create proc oooo
@xx varchar(20),
@oo int out
as 
insert into oNotice(MemoInfo)  values(@xx);
select @oo = @@identity;
go
//执行方法 先要定义输出变量 在执行的时候 还要给变量声明是输出类型
declare @oo int
exec oooo '123123123',@oo out
select @oo