sqlserver中的存储过程

sql server中的存储过程

在工作中大部分公司都喜欢用存储过程，存储过程可以重复使用，提高性能，减少网络流量，安全性高等优势。

存储过程一共分为了三类：用户定义的存储过程、扩展存储过程以及系统存储过程。

1，普通的存储过程

create proc GetCityName

as

select cityname  from city

go

 

2，带参数的存储过程

create proc GetCityName

@CityId varchar(20)

as

select cityname from city where cityid=@CityId

go

 在数据库中调用的方法：

declare @cityid varchar(20)

set @cityid=021

ecec GetCityName  @cityid 

 

3,在参数后加output表名该参数为输出参数

declare @CityCountByProvince  varchar(20)  output,@ProvinceID varchar(20)

as

select CityName from City where provinceid=@ProvinceID 

set seletc @CityCountByProvince  =count(*) from city

go

 

4,带游标的存储过程

下面是从网上看到的一段代码记录下来学校一下

 

If Object_ID('dbo.GetMasterGoods') Is Not Null
            Drop Proc dbo.GetMasterGoods
      Go

      Create Proc GetMasterGoods
      @MyCursor Cursor Varying Output
      With Encryption
      As 
             Set @MyCursor = Cursor
             For
                    Select GoodsCode,GoodsName From Master_Goods
      Open @MyCursor
      Go

      --下边建立另外一个存储过程，用于遍历游标输出结果

      Create Proc GetAllGoodsIDAndName
      As

      Declare @GoodsCode varchar(18)
      Declare @GoodsName nvarchar(20)
      Declare @MasterGoodsCursor Cursor
      Exec GetMasterGoods @MasterGoodsCursor out
      Fetch Next From @MasterGoodsCursor
      InTo @GoodsCode,@GoodsName
      While(@@Fetch_Status = 0)
      Begin
             Begin
                    Print @GoodsCode + ':' + @GoodsName
             End
             Fetch Next From @MasterGoodsCursor
             InTo @GoodsCode,@GoodsName
      End
      Close @MasterGoodsCursor
      Deallocate @MasterGoodsCursor
      Go

      最后执行Exec GetAllGoodsIDAndName结果为以下内容

      0003:品0003
      0004:品0004
      0005:123123
      0006:品0006
      0007:品0007
      0008:品0008
      0009:品0009
      0010:品0010
      0011:品0011
      0012:品0012
      0013:品0013
      0014:品0014