创建表:
USE [testa]
GO
/****** Object: Table [dbo].[AjaxCity] Script Date: 06/26/2011 14:05:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AjaxCity](
[id] [int] NOT NULL,
[cityName] [nvarchar](50) NOT NULL,
[short] [nvarchar](10) NOT NULL
) ON [PRIMARY]
GO
USE [testa]
GO
/****** Object: Table [dbo].[AjaxCounty] Script Date: 06/26/2011 14:06:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AjaxCounty](
[id] [int] NOT NULL,
[countryName] [nvarchar](50) NOT NULL,
[cityID] [int] NOT NULL
) ON [PRIMARY]
GO
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*/
print @Result
end
close city_cursor/*关闭游标*/
deallocate city_cursor/*释放游标引用*/
GO
declare @Result varchar(255)
exec mysp_Cursor @Result
print @Result