while(1=1) begin if(条件不满足) break else begin insert into #临时表 select '一组值' end end
游标,来晚了。我的一个例子,看看吧USE [JCTest] GO /****** Object: StoredProcedure [dbo].[P_TEST_PLAN_REPORT_CENTER] Script Date: 07/09/2009 14:19:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GOALTER PROCEDURE [dbo].[P_TEST_PLAN_REPORT_CENTER] @TEST_PLAN_ID INT, @BASE_EQUIPMENT_TYPE_NO VARCHAR(20) ASDECLARE @BASE_AFN_DESC varchar(200), @BASE_FN_DESC nvarchar(200),@CENTER_SUC_RATE DECIMAL,@SUCNUM FLOAT ,@ALLNUM FLOAT,@BASE_AFN_ID INT,@BASE_FN_ID INT,@FAILNUM INT ,@RATE FLOAT,@PERCENTER VARCHAR(20)
DECLARE ALL_COUNT_CURSOR CURSOR LOCAL FOR SELECT BASE_AFN_DESC ,BASE_FN_DESC,COUNT(*),BASE_AFN_ID,BASE_FN_ID FROM V_TEST_PLAN_REPORT_DETAIL WHERE TEST_PLAN_ID = @TEST_PLAN_ID AND BASE_EQUIPMENT_TYPE_NO = @BASE_EQUIPMENT_TYPE_NO GROUP BY BASE_AFN_ID,BASE_FN_ID ,BASE_AFN_DESC ,BASE_FN_DESC BEGIN CREATE TABLE #TEMP ( BASE_AFN_DESC VARCHAR(200), BASE_FN_DESC VARCHAR(200), CENTER_SUC_RATE VARCHAR(20) )
OPEN ALL_COUNT_CURSOR FETCH NEXT FROM ALL_COUNT_CURSOR INTO @BASE_AFN_DESC,@BASE_FN_DESC,@ALLNUM,@BASE_AFN_ID,@BASE_FN_ID WHILE @@FETCH_STATUS = 0 BEGIN SELECT @FAILNUM = COUNT(ISNULL(SUCCESSED,0)) FROM V_TEST_PLAN_REPORT_DETAIL WHERE TEST_PLAN_ID = @TEST_PLAN_ID AND BASE_EQUIPMENT_TYPE_NO=@BASE_EQUIPMENT_TYPE_NO AND(SUCCESSED IS NULL OR SUCCESSED =0) AND BASE_AFN_ID = @BASE_AFN_ID AND BASE_FN_ID = @BASE_FN_ID SET @SUCNUM = @ALLNUM - @FAILNUM IF(@SUCNUM IS NOT NULL) BEGIN SET @RATE = @SUCNUM/@ALLNUM IF(@RATE=1) BEGIN SET @PERCENTER = '100%' END ELSE BEGIN SET @PERCENTER =CAST(CAST(@RATE*100 AS DECIMAL(18,2)) AS VARCHAR(20))+'%' END INSERT INTO #TEMP (BASE_AFN_DESC,BASE_FN_DESC,CENTER_SUC_RATE) VALUES(@BASE_AFN_DESC,@BASE_FN_DESC,@PERCENTER) END FETCH NEXT FROM ALL_COUNT_CURSOR INTO @BASE_AFN_DESC,@BASE_FN_DESC,@ALLNUM,@BASE_AFN_ID,@BASE_FN_ID END CLOSE ALL_COUNT_CURSOR DEALLOCATE ALL_COUNT_CURSOR
SET NOCOUNT ON; SELECT * FROM #TEMP DROP TABLE #TEMP END
begin
if(条件不满足)
break
else
begin
insert into #临时表
select '一组值'
end
end
GO
/****** Object: StoredProcedure [dbo].[P_TEST_PLAN_REPORT_CENTER] Script Date: 07/09/2009 14:19:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[P_TEST_PLAN_REPORT_CENTER]
@TEST_PLAN_ID INT,
@BASE_EQUIPMENT_TYPE_NO VARCHAR(20)
ASDECLARE
@BASE_AFN_DESC varchar(200), @BASE_FN_DESC nvarchar(200),@CENTER_SUC_RATE DECIMAL,@SUCNUM FLOAT ,@ALLNUM FLOAT,@BASE_AFN_ID INT,@BASE_FN_ID INT,@FAILNUM INT
,@RATE FLOAT,@PERCENTER VARCHAR(20)
DECLARE ALL_COUNT_CURSOR CURSOR LOCAL FOR
SELECT BASE_AFN_DESC ,BASE_FN_DESC,COUNT(*),BASE_AFN_ID,BASE_FN_ID FROM V_TEST_PLAN_REPORT_DETAIL WHERE TEST_PLAN_ID = @TEST_PLAN_ID
AND BASE_EQUIPMENT_TYPE_NO = @BASE_EQUIPMENT_TYPE_NO GROUP BY BASE_AFN_ID,BASE_FN_ID ,BASE_AFN_DESC ,BASE_FN_DESC
BEGIN
CREATE TABLE #TEMP
(
BASE_AFN_DESC VARCHAR(200),
BASE_FN_DESC VARCHAR(200),
CENTER_SUC_RATE VARCHAR(20)
)
OPEN ALL_COUNT_CURSOR
FETCH NEXT FROM ALL_COUNT_CURSOR
INTO @BASE_AFN_DESC,@BASE_FN_DESC,@ALLNUM,@BASE_AFN_ID,@BASE_FN_ID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @FAILNUM = COUNT(ISNULL(SUCCESSED,0))
FROM V_TEST_PLAN_REPORT_DETAIL
WHERE TEST_PLAN_ID = @TEST_PLAN_ID AND BASE_EQUIPMENT_TYPE_NO=@BASE_EQUIPMENT_TYPE_NO
AND(SUCCESSED IS NULL OR SUCCESSED =0) AND BASE_AFN_ID = @BASE_AFN_ID
AND BASE_FN_ID = @BASE_FN_ID
SET @SUCNUM = @ALLNUM - @FAILNUM
IF(@SUCNUM IS NOT NULL)
BEGIN
SET @RATE = @SUCNUM/@ALLNUM
IF(@RATE=1)
BEGIN
SET @PERCENTER = '100%'
END
ELSE
BEGIN
SET @PERCENTER =CAST(CAST(@RATE*100 AS DECIMAL(18,2)) AS VARCHAR(20))+'%'
END
INSERT INTO #TEMP (BASE_AFN_DESC,BASE_FN_DESC,CENTER_SUC_RATE) VALUES(@BASE_AFN_DESC,@BASE_FN_DESC,@PERCENTER)
END
FETCH NEXT FROM ALL_COUNT_CURSOR
INTO @BASE_AFN_DESC,@BASE_FN_DESC,@ALLNUM,@BASE_AFN_ID,@BASE_FN_ID
END CLOSE ALL_COUNT_CURSOR
DEALLOCATE ALL_COUNT_CURSOR
SET NOCOUNT ON;
SELECT * FROM #TEMP
DROP TABLE #TEMP
END