SQL Server Function with Temp Table and Looping

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

TFS: The workspace version table contains an unknown schema version

Error: TF400018: The local version table for the local workspace MY-PC;My User could not be opened. The workspace version table contains an unknown schema version.

Fix

Backup the folder first!

  • Click the box in "Workspace".
  • Click on "Workspaces".
  • Delete the workspace profile you're currently using
  • Re-connect to TFS open "Source Control"