USE [ARTEA.MES]
GO

/****** Object: UserDefinedFunction [dbo].[UnionPart] Script Date: 11/18/2015 15:39:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  
-- Create date: 
-- Description: 
-- =============================================
ALTER FUNCTION [dbo].[UnionPart]
(
-- Add the parameters for the function here
@OrderNo varchar(30)
)
RETURNS [nvarchar](4000)
AS
BEGIN
-- Declare the return variable here
DECLARE @ReturnValue [nvarchar](4000)
SET @ReturnValue = ''
-- Add the T-SQL statements to compute the return value here
SELECT @ReturnValue=@ReturnValue + RTRIM(LTRIM(c.PartName)) + ','
FROM table1 c left join table2 d on d.OrderID=c.OrderID
WHERE d.OrderNO = @OrderNo
SET @ReturnValue = substring(@ReturnValue,1,len(@ReturnValue)-1)
RETURN @ReturnValue

END

GO

?