ALTER FUNCTION [dbo].[fnGetPipelineProductsCarried]
(
@GIS_PipelineMasterId INT,
@EnglishOrFrench NVARCHAR(20) = 'English'
)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @ReturnEnglish VARCHAR(1000) = ''
DECLARE @ReturnFrench VARCHAR(1000) = ''
DECLARE @ProductCarriedName NVARCHAR(255) = ''
DECLARE @ProductCarriedNameFrench NVARCHAR(255) = ''
DECLARE @TempTable TABLE (
PipelineName NVARCHAR(255),
ProductCarriedName NVARCHAR(255),
ProductCarriedNameFrench NVARCHAR(255),
SUBABC NVARCHAR(5),
PercentH2S DECIMAL(18,3),
GIS_ProductCarriedTypeId INT,
GIS_PipelineMasterId INT,
DummyID INT IDENTITY(1,1) NOT NULL
)
INSERT INTO @TempTable
SELECT DISTINCT
pm.PipelineName,
pct.ProductCarriedName,
pct.ProductCarriedNameFrench,
pspc.SUBABC,
pspc.PercentH2S,
pspc.GIS_ProductCarriedTypeId,
ps.GIS_PipelineMasterId
FROM ((GIS_PipelineSegment ps
INNER JOIN GIS_PipelineSegmentProductCarried pspc ON ps.GIS_PipelineSegmentId = pspc.GIS_PipelineSegmentId)
INNER JOIN GIS_PipelineMaster pm ON ps.GIS_PipelineMasterId = pm.GIS_PipelineMasterId)
INNER JOIN GIS_ProductCarriedType pct ON pspc.GIS_ProductCarriedTypeId = pct.GIS_ProductCarriedTypeId
WHERE pm.GIS_PipelineMasterId = @GIS_PipelineMasterId
ORDER BY pm.PipelineName,
pspc.SUBABC;
--Counters
DECLARE @count INT
DECLARE @count_max INT
SET @count = 1
SELECT @count_max = COUNT(DummyID) FROM @TempTable
WHILE(@count <= @count_max)
BEGIN
SELECT @ProductCarriedName = ProductCarriedName,
@ProductCarriedNameFrench = ProductCarriedNameFrench
FROM @TempTable
WHERE DummyID = @count
SET @ReturnEnglish = @ReturnEnglish + @ProductCarriedName + ', '
SET @ReturnFrench = @ReturnFrench + @ProductCarriedNameFrench + ', '
--Must Always be at the end of the Loop
SET @count = @count + 1
END
IF @EnglishOrFrench = 'English'
BEGIN
SET @ReturnEnglish = CASE @ReturnEnglish
WHEN NULL THEN NULL
ELSE (CASE LEN(@ReturnEnglish)
WHEN 0 THEN @ReturnEnglish
ELSE LEFT(@ReturnEnglish, LEN(@ReturnEnglish) - 1)
END )
END
RETURN @ReturnEnglish
END
ELSE
BEGIN
SET @ReturnFrench = CASE @ReturnFrench
WHEN NULL THEN NULL
ELSE (CASE LEN(@ReturnFrench)
WHEN 0 THEN @ReturnFrench
ELSE LEFT(@ReturnFrench, LEN(@ReturnFrench) - 1)
END )
END
RETURN @ReturnFrench
END
RETURN @ReturnEnglish
END