Function

CREATE FUNCTION [dbo].[xxx_QuoteItems] ( @ID INT ) RETURNS nVarChar(MAX) AS BEGIN DECLARE @QuoteItems nVarChar(MAX) DECLARE @prod AS NVARCHAR(MAX) DECLARE @proddesc AS NVARCHAR(MAX) DECLARE @Qty AS Numeric(24,2) DECLARE @QLID AS INT DECLARE @QLOQID AS INT DECLARE cursor_qitems CURSOR FOR SELECT Prod_code, QuIt_description,Quit_quantity, QuIt_LineItemID,QuIt_orderquoteid FROM QuoteItems (NOLOCK) LEFT OUTER JOIN NewProduct ON QuIt_productid = Prod_ProductID WHERE QuIt_orderquoteid = @ID AND QuIt_Deleted IS NULL ORDER BY QuIt_linenumber OPEN cursor_qitems FETCH NEXT FROM cursor_qitems INTO @prod, @proddesc, @qty, @qlid, @QLOQID WHILE @@FETCH_STATUS = 0 BEGIN SET @QuoteItems = ISNULL(@QuoteItems+CHAR(10),'')+(ISNULL('Prod Code:'+@prod+' || ','')) +@proddesc +' || ' +ISNULL('Qty:'+CAST(CAST(@qty AS Numeric(24,2)) AS nVarChar(30)),'')
FETCH NEXT FROM cursor_qitems INTO @prod, @proddesc, @qty, @qlid, @QLOQID END CLOSE cursor_qitems DEALLOCATE cursor_qitems RETURN @QuoteItems END GO