title: Handling larger strings in Sql Server 2008
body: <p>We have an stored procedure that we created so that user can write comma separated search tags in their software product's admin. So he can add comma-separated tags and in case if he wants to edit them, we read from the table all the tags, recreate them as comma-separated values (CSV) in stored procedure and returns that to the calling code. What happened recently, the user complained that he could not see the new CSVs he wrote. I looked into it and found out that the stored procedure is truncating the string when it reads values from database and creates CSV string. The string is of type nvarchar, and because its exceeding the max characters of 4000 limit, the values gets truncated. Any ideas on how to work out that problem.</p>
  
  <p>Find my code underneath.</p>
  
  <pre><code>BEGIN
  BEGIN 
      Declare @Synonyms Table
      (
          RowID int Identity(1,1),
          SynonymID int,
          [Synonym] nvarchar(4000)
      );
  
      SET NOCOUNT ON;
  
      Insert @Synonyms(SynonymID, [Synonym])
      Select distinct SynonymID, [Synonym] From RF_SearchSynonyms with(nolock) Where SearchTermID = @SearchTermID And ActiveInd = 1
  
      If((Select COUNT(RowID) From @Synonyms) &lt;&gt; 0)
      BEGIN
          Declare @CurrentRow int = (Select MIN(RowID) From @Synonyms),
                  @TotalRows int = (Select MAX(RowID) From @Synonyms),
                  @Synonyms_CSV nvarchar(4000) = '';
  
  
          WHILE @CurrentRow &lt;= @TotalRows
          BEGIN
              Declare @TempSyn nvarchar(500);
              Select @TempSyn = [Synonym] + ',' From @Synonyms Where RowID = @CurrentRow;
              Set @Synonyms_CSV = @Synonyms_CSV + LTRIM(RTRIM(LOWER(@TempSyn)));
  
              SET @CurrentRow = @CurrentRow + 1
          END
      END
      Else
      BEGIN
          Set @Synonyms_CSV = '';
      END
  END
  
  BEGIN
      Declare @SKUs Table
      (
          RowID int Identity(1,1),
          SkuID int,
          SKU nvarchar(15)
      );
  
      SET NOCOUNT ON;
  
      Insert @SKUs(SkuID, SKU)
      Select distinct SkuID, SKU From RF_SearchSkus with(nolock) Where SearchTermID = @SearchTermID And ActiveInd = 1
  
      If((Select COUNT(RowID) From @SKUs) &lt;&gt; 0)
      BEGIN
          Declare @CurrentRow1 int = (Select MIN(RowID) From @SKUs),
                  @TotalRows1 int = (Select MAX(RowID) From @SKUs),
                  @Skus_CSV nvarchar(4000) = '';
  
  
          WHILE @CurrentRow1 &lt;= @TotalRows1
          BEGIN
              Declare @TempSku nvarchar(15);
              Select @TempSku = SKU + ',' From @SKUs Where RowID = @CurrentRow1;
              Set @Skus_CSV = @Skus_CSV + LTRIM(RTRIM(@TempSku));
  
              SET @CurrentRow1 = @CurrentRow1 + 1
          END
      END
      Else
      BEGIN
          Set @Skus_CSV = '';
      END
  END
  
  BEGIN
      Declare @Combined varchar(8000),
              @syn_len int = 0,
              @sku_len int = 0;
  
      Select @syn_len = LEN(@Synonyms_CSV);
      Select @sku_len = LEN(@Skus_CSV);
      Select @Combined = @Synonyms_CSV + '-_-' + @Skus_CSV;
  
      Select @Synonyms_CSV + '-_-' + @Skus_CSV;
  END
  </code></pre>
  
  <p>END</p>
  
  <p>I can't use text and ntext as they do not play nice with concatenation operations.</p>
  
  <p>Thanks.</p>