存储过程
建表
存储过程详解
if ( OBJECT_ID( 'proc_get_syudent' , 'p' ) is not null )
drop proc proc_get_syudent
go
create proc proc_get_syudent
as
select * from student;
exec proc_get_syudent;
alter proc proc_get_syudent
as
select * from student where id > 2
exec proc_get_syudent;
if ( OBJECT_ID( 'proc_get_syudent' , 'p' ) is not null )
drop proc proc_get_syudent
go
create proc proc_find_stu( @stratId int , @endId int )
as
select * from student where id between @stratId and @endId
go
exec proc_find_stu 4 , 5 ;
if ( OBJECT_ID( 'proc_find_stuByName' , 'p' ) is not null )
drop proc proc_get_syudent
go
create proc proc_find_stuByName( @name varchar ( 20 ) = '%j%' , @nextName varchar ( 20 ) = '%' )
as
select * from student where name like @name and name like @nextName ;
exec proc_find_stuByName;
exec proc_find_stuByName '%欧%' ;
exec proc_find_stuByName '%浪%' ;
exec proc_find_stuByName '%浪' , '%欧%' ;
create proc insertStudentTest
as
declare @i1 int
set @i1 = 0
while @i1 < 10
begin
insert into student( name, sex, age) values ( '测试' , '男' , @i1 ) ;
set @i1 = @i1 + 1
end
exec insertStudentTest
select * from student
create proc pro2
@id1 int ,
@name1 varchar ( 20 ) output,
@sex1 varchar ( 20 ) output
as
begin
select @name1 = name, @sex1 = sex from student where id= @id1
end
declare @name2 varchar ( 20 ) , @sex2 varchar ( 20 ) ;
exec pro2 @name1 = @name2 output, @sex1 = @sex2 output, @id1 = 2
select @name2 , @sex2
实际操作一遍(这里不赘述,自己写)