【基础】常用数据库脚本

SQL Server数据库：

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

CREATE PROCEDURE mysp_para
            @CityName varchar(255),
            @Short    varchar(255)
       AS
         select * from AjaxCity where CityName=@CityName And Short=@Short
       GO

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

CREATE PROCEDURE mysp_output
       @SUM int  output
 AS
       select @SUM=sum([ID]) from (select top 2 * from AjaxCity) as tmpTable
GO

 

统计出各个地级市下面的县级市的个数,并组成一个字符串.结果应该是"5,2,2".：

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

 。。