-- =============================================
-- returns all the POs on a Contract
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'ddf_PopPosOnAContract')
DROP FUNCTION ddf_PopPosOnAContract
GO
CREATE FUNCTION dbo.ddf_PopPosOnAContract (@POPCONTNUM varchar(21))
RETURNS VARCHAR(200)
-- select dbo.ddf_PopPosOnAContract ('test1')
-- select dbo.ddf_PopPosOnAContract ('test2')
AS
BEGIN
DECLARE @vchrOut AS VARCHAR(200)
SELECT @vchrOut = coalesce(@vchrOut + ', ','') + rtrim(ponumber)
FROM (
select distinct PONUMBER
from (
select po.ponumber, popcontnum
from pop10100 po with (nolock)
WHERE POPCONTNUM = @POPCONTNUM
union all
select ponumber, popcontnum
from pop30100 with (nolock)
WHERE POPCONTNUM = @POPCONTNUM
) t
) t
return isnull(@vchrout,'')
END
GO