我想用Union来使用游标 [英] I want to use cursor with Union

问题描述

declare @PhaseDate datetime
declare @DueDate datetime
declare @CUST_ID bigint
declare @FLAT_ID bigint
	DECLARE @toLast CURSOR
SET @toLast = CURSOR FOR
select FLAT_ID,CUST_ID,DueDate,PhaseDate from TBL_FLATMSTR where TOTAL_VALUE!=null
OPEN @toLast
FETCH NEXT
FROM @toLast INTO @FLAT_ID,@CUST_ID,@DueDate,@PhaseDate
WHILE @@FETCH_STATUS = 0
BEGIN

exec rptPhaseAllDeatils @PhaseDate ,@DueDate  ,@CUST_ID ,@FLAT_ID-- this query returning me temp table data like (select * from temp)
union--Here i want help
FETCH NEXT
FROM @toLast INTO @FLAT_ID,@CUST_ID,@DueDate,@PhaseDate
END
CLOSE @toLast
DEALLOCATE @toLast
END

每次调用rptPhaseAllDeatils proc我只需清空所有数据和填充新数据

现在我想要所有合并数据

every time i call rptPhaseAllDeatils proc i just flush all data and fill with new data
now i want all merge data

推荐答案

如果（比方说）设置了附加参数，您可能最好更改存储过程以提供额外数据。对此帖的回复有好评 http://stackoverflow.com/questions/5292069 / union-of-multiple-stored-procedures [ ^ ]

请注意，只有当您的SP返回 bigint，bigint，datetime，datetime时，联合才有效

You might be better off changing your stored procedure to provide the extra data if (say) an additional parameter is set. Good comments on responses to this post http://stackoverflow.com/questions/5292069/union-the-results-of-multiple-stored-procedures[^]
Note that the union will only work if your SP returns bigint, bigint, datetime, datetime

我知道这已经解决了，但我认为解释答案和另一种方法可能很有教育意义。

I know this has already been solved, but I thought it might be educational to spell out the answer and an alternative approach.

DECLARE @PhaseDate DATETIME;
DECLARE @DueDate DATETIME;
DECLARE @CUST_ID BIGINT;
DECLARE @FLAT_ID BIGINT;

--Temporary table to accumulate the results into.
CREATE TABLE #holdingTable (
	PhaseDate DATETIME,
	DueDate DATETIME,
	CUST_ID BIGINT,
	FLAT_ID BIGINT
	--List the other columns returned from rptPhaseAllDeatils here.
	);

--Fast_forward cursors are faster than default ones.
DECLARE toLast CURSOR LOCAL FAST_FORWARD FOR
	SELECT FLAT_ID, CUST_ID, DueDate, PhaseDate
	FROM TBL_FLATMSTR
	WHERE TOTAL_VALUE != NULL;
OPEN toLast;

--The "WHILE(1 = 1) BEGIN ... FETCH ... IF (@@Fetch_Status > 0) BREAK" pattern
--means we don't need to repeat the FETCH statement (so less typing).
WHILE (1 = 1) BEGIN
	FETCH toLast INTO @FLAT_ID, @CUST_ID, @DueDate, @PhaseDate;
	IF (@@FETCH_STATUS > 0) BREAK;

	--Append output of "rptPhaseAllDeatils" sproc to our temporary table.
	INSERT INTO #holdingTable
		EXEC rptPhaseAllDeatils @PhaseDate, @DueDate, @CUST_ID, @FLAT_ID;
END
CLOSE toLast;
DEALLOCATE toLast;

--Return the accumulated results.
SELECT * FROM #holdingTable;

更快的替代方案是创建新版本的 rptPhaseAllDeatils 它包含 TOTAL_VALUE！= NULL 过滤器。这比使用临时表和光标进行捣乱要快得多。

A faster alternative would be to create a new version of rptPhaseAllDeatils that incorporates the TOTAL_VALUE != NULL filter. That would be much faster than mucking about with a temporary table and a cursor.

这篇关于我想用Union来使用游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！