一:遍历 数据表
select HID into #temp from MD_SheetTemplate where SheetStateID=1
declare @HID varchar(50)
WHILE EXISTS( select HID from #temp ) ---遍历临时表
begin
select top 1 @HID=HID from #temp
print @HID
delete from #temp where hid=@HID
end
二:遍历字符串
ALTER PROCEDURE [dbo].[sp_pro_pw]
@wcode varchar(10),
@pcode varchar(500)
AS
BEGIN
SET NOCOUNT ON;
set @pcode=@pcode+','; --删除最后一个,因为最后一个后面没有逗号,所以在循环中跳出,需另外再删除
DECLARE @PointerPrev int
DECLARE @PointerCurr int
DECLARE @TId int
DECLARE @COUNT int
DECLARE @Newcode varchar(500)
Set @PointerPrev=1
while (@PointerPrev < LEN(@pcode))
Begin
Set @PointerCurr=CharIndex(',',@pcode,@PointerPrev)
if(@PointerCurr>0)
Begin
set @TId=cast(SUBSTRING(@pcode,@PointerPrev,@PointerCurr-@PointerPrev) as int)
select @COUNT=count(*) from zCheckWork_WP where wcode=@wcode and pcode=@TId
if(@COUNT=0)
begin
INSERT INTO [zCheckWork_WP]
([wCode]
,[PCode])
VALUES(@wcode,@TId);
end
SET @PointerPrev = @PointerCurr+1
End
else
Break
End
--删除已存在的数据
delete from zCheckWork_WP where wcode=@wcode and pcode not in( select str2table from StrToTable(@pcode))
return 1
END