T-SQL游标的使用范例

--定义变量
declare @i INT,@j int
declare @Propertyid CHAR(50)
declare @Title CHAR(50)
declare @RoomPic1 CHAR(50)
declare @RoomPic2 CHAR(50)
declare @RoomPic3 CHAR(50)
declare @HouseTypeShow1 CHAR(50)
declare @HouseTypeShow2 CHAR(50)
declare @HouseTypeShow3 CHAR(50)
declare @ComunityShow1 CHAR(50)
declare @ComunityShow2 CHAR(50)
declare @ComunityShow3 CHAR(50)
declare @VidCity CHAR(50)
declare @str char(50)
--定义游标把PropertyAds 的值赋给mycursor 游标
declare mycursor cursor for select PropertyId,Title,RoomPic1,RoomPic2,RoomPic3,HouseTypeShow1,HouseTypeShow2,HouseTypeShow3,ComunityShow1,ComunityShow2,ComunityShow3,VidCity from PropertyAds where Title is not null 
--declare mycursor cursor for select img,UseAs from EstateImgs
open mycursor --打开游标
select @i=count(*) from PropertyAds where Title is not null  --得到记录数

-- fetch next from mycursor INTO 将游标中值赋给你定义的变量

fetch next from mycursor INTO @Propertyid,@Title,@RoomPic1,@RoomPic2,@RoomPic3,@HouseTypeShow1,@HouseTypeShow2,@HouseTypeShow3,@ComunityShow1,@ComunityShow2,@ComunityShow3,@VidCity

--开始循环@@fetch_status=0

while @@fetch_status=0 and @i>1
----esnt 户ehxt--小区ehjt
BEGIN
    set @i=@i-1 
    --里面是你想要进行字段的判断和一系列的操作
if(LEFT(@RoomPic1,4)='2011')
begin
----PRINT @Propertyid+' '+@Title+' '+@RoomPic1+' '+'esnt'+convert(varchar,getdate(),108) +' '+@VidCity
----set @j=@j+1
insert into PropertyImgs values(@Propertyid,@Title,@RoomPic1,'esnt',getdate(),null,@VidCity)
end 
if(LEFT(@RoomPic2,4)='2011')
begin
----PRINT @Propertyid+' '+@Title+' '+@RoomPic2+' '+'esnt'+convert(varchar,getdate(),108) +' '+@VidCity
insert into PropertyImgs values(@Propertyid,@Title,@RoomPic2,'esnt',getdate(),null,@VidCity)
-- --set @j=@j+1
end 
if(LEFT(@RoomPic3,4)='2011')
begin
--PRINT @Propertyid+' '+@Title+' '+@RoomPic3+' '+'esnt'+convert(varchar,getdate(),108) +' '+@VidCity
insert into PropertyImgs values(@Propertyid,@Title,@RoomPic1,'esnt',getdate(),null,@VidCity)
--set @j=@j+1
end 
if(LEFT(@HouseTypeShow1,4)='2011')
begin
--PRINT @Propertyid+' '+@Title+' '+@HouseTypeShow1+' '+'esnt'+convert(varchar,getdate(),108) +' '+@VidCity
insert into PropertyImgs values(@Propertyid,@Title,@HouseTypeShow1,'ehxt',getdate(),null,@VidCity)
--set @j=@j+1
end 
if(LEFT(@HouseTypeShow2,4)='2011')
begin
--PRINT @Propertyid+' '+@Title+' '+@HouseTypeShow2+' '+'esnt'+convert(varchar,getdate(),108) +' '+@VidCity
--set @j=@j+1
insert into PropertyImgs values(@Propertyid,@Title,@HouseTypeShow2,'ehxt',getdate(),null,@VidCity)
end 
if(LEFT(@HouseTypeShow3,4)='2011')
begin
--PRINT @Propertyid+' '+@Title+' '+@HouseTypeShow3+' '+'esnt'+convert(varchar,getdate(),108) +' '+@VidCity
--set @j=@j+1
insert into PropertyImgs values(@Propertyid,@Title,@RoomPic1,'ehxt',getdate(),null,@VidCity)
end 
if(LEFT(@ComunityShow1,4)='2011')
begin
--PRINT @Propertyid+' '+@Title+' '+@ComunityShow1+' '+'esnt'+convert(varchar,getdate(),108) +' '+@VidCity
--set @j=@j+1
insert into PropertyImgs values(@Propertyid,@Title,@ComunityShow1,'ehjt',getdate(),null,@VidCity)
end 
if(LEFT(@ComunityShow2,4)='2011')
begin
--PRINT @Propertyid+' '+@Title+' '+@ComunityShow2+' '+'esnt'+convert(varchar,getdate(),108) +' '+@VidCity
--set @j=@j+1
insert into PropertyImgs values(@Propertyid,@Title,@ComunityShow2,'ehjt',getdate(),null,@VidCity)
end 
if(LEFT(@ComunityShow3,4)='2011')
begin
--PRINT @Propertyid+' '+@Title+' '+@ComunityShow3+' '+'esnt'+convert(varchar,getdate(),108) +' '+@VidCity
--set @j=@j+1
insert into PropertyImgs values(@Propertyid,@Title,@ComunityShow3,'ehjt',getdate(),null,@VidCity)
end 
    
PRINT @RoomPic1 + ' ' + @RoomPic2+ '  '+@str

print @j

--这下面一句必须要有的也下最重要的一句重新赋值

fetch next from mycursor INTO @Propertyid,@Title,@RoomPic1,@RoomPic2,@RoomPic3,@HouseTypeShow1,@HouseTypeShow2,@HouseTypeShow3,@ComunityShow1,@ComunityShow2,@ComunityShow3,@VidCity
END
close mycursor --关闭游标
deallocate mycursor