存储过程

从网上找资料学习了存储过程，编写了3个小例子如下：

1、带两个参数的存储过程

CREATE PROCEDURE dbo.twoparam

@age varchar(30),@name varchar(50)

AS

BEGIN

 

 select * from proctest where name=@name and age=@age

 

 

END

 

EXECUTE twoparam 'male','33'

 

 

2、带返回值的存储过程

CREATE PROCEDURE dbo.test(@sage int=null)

 

as

if @sage is null

begin

print '必须传参'

return 13

end

if not EXISTS(select * from proctest wheremoney>@sage)

begin

print('没有数据')

return -103

end

 

3、带游标的存储过程

select * from proctest where money>@sage

return 0

 

declare @count int

execute @count=test 22

print @count

 

CREATE PROCEDURE dbo.curtest

AS

BEGIN

  /*Procedure body */

 declare cur1 cursor for select Id, name,age,money from proctest

 

 declare @id int,@sname varchar(20) ,@sage varchar(20),@smoney int

 open cur1

 fetch next from cur1 into @id,@sname,@sage,@smoney

   While(@@fetch_status = 0)

   begin

   if @sage='女'

   begin

   set @sage='female'

   update  proctest set age=@sagewhere id=@id

   end

   

   else

   

     begin

     set @sage='male'

     update  proctest set age=@sagewhere id=@id

     END

     PRINT(@sname+'...'+@sage+'....')

  

    

    fetch next from cur1 into @id, @sname,@sage,@smoney

     

   end

 close cur1

 deallocate cur1

END