在 Sql Server 中，如何将游标中的值放入临时表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24915301/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In Sql Server, how do you put value from cursor into temp table?
提问by Arif YILMAZ
I am trying to create a function which has a cursor in it. I want to get the Quanatity value from that cursor and put it in the temp table. But I havent succeeded to get the value and put it into the temp table.
我正在尝试创建一个包含游标的函数。我想从该游标中获取 Quanatity 值并将其放入临时表中。但是我还没有成功获取该值并将其放入临时表中。
I put comment where I couldnt get it done...
我把评论放在我无法完成的地方...
here is my code
这是我的代码
alter FUNCTION test(@input VARCHAR(250)) RETURNS Decimal(8, 2) AS BEGIN
DECLARE @rst Decimal(8, 2) SET @rst=0
DECLARE @Temp TABLE (Quantity Decimal(8,2), Price Decimal(8,2))
DECLARE @amount Decimal(8,2)
DECLARE @price Decimal(8,2)
DECLARE CrsOrfLine CURSOR FOR
SELECT AMOUNT FROM LG_001_01_ORFLINE
WHERE LINETYPE = 0
AND ORDFICHEREF = (SELECT TOP 1 LOGICALREF FROM LG_001_01_ORFICHE WHERE GUID='EEB44E72-3717-4F5B-8F7E-6A36EB38EA22')
ORDER BY LINENO_ ASC;
FETCH NEXT FROM CrsOrfLine INTO @amount
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @Temp (Quantity)
/* HOW AM I SUPPOSED TO ADD IT INTO THE TEMP?????? */
/* I COULDNT FIGURE THIS PART OUT */
FETCH NEXT FROM CrsOrfLine INTO @amount
END /*WHILE*/
CLOSE CrsOrfLine
DEALLOCATE CrsOrfLine
回答by SQLChao
You can do the following. Note that it only inserts the quantity so it needs to be modified if you intend to include the price.
您可以执行以下操作。请注意，它仅插入数量，因此如果您打算包括价格，则需要对其进行修改。
DECLARE @Temp TABLE
(
Quantity Decimal(8,2),
Price Decimal(8,2)
)
INSERT INTO @temp (Quantity)
SELECT AMOUNT FROM LG_001_01_ORFLINE
WHERE LINETYPE = 0
AND ORDFICHEREF = (SELECT TOP 1 LOGICALREF FROM LG_001_01_ORFICHE WHERE GUID='EEB44E72-3717-4F5B-8F7E-6A36EB38EA22 ORDER BY LINENO_ ASC')
回答by Mohit Jariwal
CREATE PROCEDURE [dbo].[usp_demo_cursor_with_temp_table]
AS
BEGIN
DECLARE @temp TABLE (value1 varchar(5),value2 varchar(5),value3 INT,value4 varchar(1))
DECLARE @value1 varchar(5)
DECLARE @value2 varchar(5)
DECLARE @value3 INT
DECLARE @value4 varchar(5)
DECLARE check_data_cursor CURSOR FOR
select distinct value1,value2,value3,value4 from table_name where status = 'A'
OPEN check_data_cursor
FETCH NEXT FROM check_data_cursor INTO @value1,@value2,@value3,@value4
WHILE (@@FETCH_STATUS <> -1)
BEGIN
-- any business logic + temp inseration
insert into @temp values (@tickerCode,@quarter,@financial_year,@status)
END
FETCH NEXT FROM check_data_cursor INTO @value1,@value2,@value3,@value4
END
CLOSE check_data_cursor
DEALLOCATE check_data_cursor
-- to view temp data
select * from @temp
END
回答by C.J.
Edited: This should help taking care of the price. Since the price is coming from a different Selectstatement, you may need a joinhere.
编辑：这应该有助于照顾价格。由于价格来自不同的Select声明，您可能需要在join这里。
INSERT INTO @Temp (Quantity, Price)
(SELECT AMOUNT FROM LG_001_01_ORFLINE
WHERE LINETYPE = 0
AND ORDFICHEREF = (SELECT TOP 1 LOGICALREF FROM LG_001_01_ORFICHE WHERE GUID='EEB44E72-3717-4F5B-8F7E-6A36EB38EA22' ORDER BY LINENO_ ASC)) T1
JOIN
(SELECT ORG_PRICE FROM LG_XT002001_001 XT002 WHERE XT002.ORF_GUID='EEB44E72-3717-4F5B-8F7E-6A36EB38EA22' ORDER BY ORFLINE_NO ASC) T2
ON T1.Primary_Key = T2.Primary_Key
