CREATE FUNCTION [_SMDBA_].[GET_PR_LINE_ITEMS]
(
@PRNo int
)
RETURNS nvarchar(4000)
AS
BEGIN
-- Declare the return variable here
DECLARE @LineItems nvarchar(4000)
SET @LineItems = 'Description' + CHAR(9) + 'Unit Price' + CHAR
(9) + 'Qty Ord' + CHAR(9) + 'Amount' + CHAR(10)

DECLARE @Description nvarchar(50),
@UnitPrice float,
@QtyOrd smallint,
@Amount float

-- Add the T-SQL statements to compute the return value here
DECLARE cur CURSOR

FOR SELECT [Description],
[Unit Price],
[Qty Ord],
[Amount]
FROM _SMDBA_.[Purchasing Items]
WHERE [ReqN] = @PRNo
AND [InActive:] = 0

OPEN cur
FETCH NEXT FROM cur INTO

@Description,
@UnitPrice,
@QtyOrd,
@Amount

WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @LineItems = @LineItems + @Description
+
CHAR(9) + '£' + CONVERT(nvarchar(10),
@UnitPrice) +
CHAR(9) + CAST(@QtyOrd As nvarchar
(4)) +
CHAR(9) + '£' + CONVERT(nvarchar(10),
@Amount) +
CHAR(10)
END
FETCH NEXT FROM cur INTO

@Description,
@UnitPrice,
@QtyOrd,
@Amount

END

CLOSE cur
DEALLOCATE cur

-- Return the result of the function
RETURN @LineItems

END