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