In Sql Server, how do you put value from cursor into temp table?

Question

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.

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

Solution 1

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')

Solution 2

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

Solution 3

Edited: This should help taking care of the price. Since the price is coming from a different Select statement, you may need a join here.

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

Comments

[Arif YILMAZ]

yes, I need to add price as well but I am not sure how to update it. any idea?

[SQLChao]

How do you get the price? Does it come from the same SELECT?

[Arif YILMAZ]

SELECT ORG_PRICE FROM LG_XT002001_001 XT002 WHERE XT002.ORF_GUID='EEB44E72-3717-4F5B-8F7E-6A36EB38EA22' ORDER BY ORFLINE_NO ASC;

[SQLChao]

I'd rewrite the query to get the 2 columns in one select if possible. Then you could either remove (quantity) from my query or change it to (quantity, price).