CREATE FUNCTION [_SMDBA_].[GET_PR_LINE_ITEMS_HTML]
(
@PRNo int
)
RETURNS nvarchar(4000)
AS
BEGIN
-- Declare the return variable here
DECLARE @LineItems nvarchar(4000)
SET @LineItems = '<tr><td>' + 'Description' + '</td><td>' + 'Unit Price' + '</td><td>' + 'Qty Ord' + '</td><td>' + 'Amount' + '</td></tr>'

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 + '<tr><td>' + @Description + '</td><td>' + '$' + CONVERT(nvarchar(10), @UnitPrice) +
'</td><td>' + CAST(@QtyOrd As nvarchar(4)) + '</td><td>' + '$' + CONVERT(nvarchar(10),@Amount) + '</td></tr>'
END
FETCH NEXT FROM cur INTO

@Description,
@UnitPrice,
@QtyOrd,
@Amount

END

CLOSE cur
DEALLOCATE cur

-- Return the result of the function
RETURN @LineItems

END