第一次写的存储过程

USE [hr]
GO
/****** Object:  StoredProcedure [dbo].[Search_nosuit]    Script Date: 06/26/2012 12:25:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Search_nosuit]
	-- Add the parameters for the stored procedure here
	
AS
declare	@Problem bit,@RoleUtime time,@RoleBtime time,@Record_Time time,@Record_id int
	
Declare StaRole cursor for
select RoleUtime,RoleBtime From RoleTable

declare AttRecord Cursor for
Select Record_id,Record_Time From HR_Record 
open AttRecord
fetch next from AttRecord into @Record_id,@Record_Time
While @@FETCH_STATUS=0
BEGIN
	Set @Problem=1
	open StaRole
	fetch next from StaRole into @RoleUtime,@RoleBtime
	While @@FETCH_STATUS=0	
	Begin
		if @Record_time>@RoleUtime and @Record_Time<@RoleBtime
			Set @problem=0
	end
	close staRole
	if @Problem=1 
	begin
		insert into ProblemTable  (record_id) values (@Record_id)
		update HR_Record set Action=1 Where Record_id=@Record_id
	end    
END
close AttRecord

运行出错感觉进入了死循环，参考资料后发现确实是死循环中。

修改后的过程：

USE [hr]
GO
/****** Object:  StoredProcedure [dbo].[Search_nosuit]    Script Date: 06/26/2012 14:09:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Search_nosuit]
	-- Add the parameters for the stored procedure here
	
AS
declare	@Problem bit,@RoleUtime time,@RoleBtime time,@Record_Time time,@Record_id int
	
Declare StaRole cursor for
select RoleUtime,RoleBtime From RoleTable

declare AttRecord Cursor for
Select Record_id,Record_Time From HR_Record 
open AttRecord
fetch next from AttRecord into @Record_id,@Record_Time
While @@FETCH_STATUS=0
BEGIN
	Set @Problem=1
	open StaRole
	fetch next from StaRole into @RoleUtime,@RoleBtime
	While @@FETCH_STATUS=0	
	Begin
		if @Record_time>@RoleUtime and @Record_Time<@RoleBtime
			Set @problem=0
		fetch next from starole into @RoleUtime,@RoleBtime
	end
	close staRole
	
	if @Problem=1 
	begin
		insert into ProblemTable  (record_id) values (@Record_id)		
	end    
	update HR_Record set Action=1 Where Record_id=@Record_id
	fetch next from AttRecord into @Record_id,@Record_Time
END
close AttRecord
deallocate starole
deallocate AttRecord

OK ，完成了。

再改：

[sql]  view plain copy