存储过程实例大全

存储过程几个实例

1.选择表中所有内容并返回一个数据集

CREATE PROCEDURE mysp_All

AS

select * from AjaxCity

GO

2.根据传入的参数进行查询并返回一个数据集

CREATE PROCEDURE mysp_para

@CityName varchar(255),

@Short varchar(255)

AS

select * from AjaxCity where CityName=@CityName And Short=@Short

GO

3.带有输出参数的存储过程(返回前两条记录的ID的和)

CREATE PROCEDURE mysp_output

@SUM int output

AS

select @SUM=sum([ID]) from (select top 2 * from AjaxCity) as tmpTable

GO

4.在存储过程中使用游标

CREATE PROCEDURE mysp_Cursor

@Result varchar(255) output//声明输出变量

AS

declare city_cursor cursor for//声明游标变量

select [ID] from AjaxCity

set @Result=''

declare @Field int//声明临时存放CityID的变量

open city_cursor //打开游标

fetch next from city_cursor into @Field//将实际ID赋给变量

while(@@fetch_status=0)//循环开始

begin

if @Result = ''

select @Result = convert(nvarchar(2),count(*)) from AjaxCounty where CityID=@Field

else

select @Result = @Result + ',' + convert(nvarchar(2),count(*)) from AjaxCounty where CityID=@Field

fetch next from city_cursor into @Field//下一个CityID

end

close city_cursor//关闭游标

deallocate city_cursor//释放游标引用

GO

本文地址：http://www.45fan.com/a/question/73731.html