Reduce Code and Server Roundtrips: Listing 2: SQL Server: Parse the @territories String

On the server, the @territories string is parsed into rows by splitting on the row delimiter (the "@@"). Each row is then further parsed into columns by splitting on the pipe "|" All of the parsing logic is performed inside of two nested WHILE loops. The resulting values are stored as rows in a table variable. The table variable is used to filter the appropriate records from the Orders table. It is joined to the Orders table on the ID field, thus only returning those orders whose TerritoryIds match the territories in the table variable. The sample code sends both the territory id and territory name values to the stored procedure. This is done purposefully to illustrate how complex the server-side code can get when using delimited strings as a solution to this problem. The complexity increases because multiple loops are required to parse each row and then the columns within the row. Multiple row and column delimiter positions have to be tracked as well.

CREATE procedure [dbo].[
   up_GetOrdersByTerritory_Delimited] (
   @territories varchar(max)
)

-- TEST: up_GetOrdersByTerritory_Delimited
--  '1|Northwest@@2|Northeast@@4|Southwest@@'
AS

DECLARE @territoryList TABLE (
   ID int,
   TerritoryName varchar(50)
)

BEGIN
      
   SET NOCOUNT ON
   
   DECLARE @columnDelimiter char(1) = '|'
   DECLARE @rowDelimiter char(2) = '@@'
   DECLARE @columnDelimiterPosition int = 0
   DECLARE @previousColumnDelimiterPosition int = 0
   DECLARE @rowDelimiterPosition bigint = 0
   DECLARE @previousRowDelimiterPosition bigint = 0
   DECLARE @rowValues varchar(100)

   SELECT   @rowDelimiterPosition = 
         CHARINDEX(@rowDelimiter, @territories, 
         @rowDelimiterPosition)

   WHILE (@rowDelimiterPosition > 0)
   BEGIN

         SET   @rowValues = SUBSTRING(@territories, 
               @previousRowDelimiterPosition, 
               @rowDelimiterPosition – 
               @previousRowDelimiterPosition)
         SET   @columnDelimiterPosition = 
               CHARINDEX(@columnDelimiter, 
               @rowValues, 
               @previousColumnDelimiterPosition)

         WHILE (@columnDelimiterPosition > 0)
         BEGIN

            -- parse the string into indivual territory id and 
            -- name values. Insert each row into the 
            -- @territoryList table variable
            INSERT INTO @territoryList(ID, TerritoryName)
            SELECT  CAST(SUBSTRING(@rowValues, 
                  @previousColumnDelimiterPosition, 
                  @columnDelimiterPosition – 
                  @previousColumnDelimiterPosition) as int),
                  SUBSTRING(@rowValues,
                  @columnDelimiterPosition + 1, 
                  @rowDelimiterPosition – 
                  @previousColumnDelimiterPosition)

            SET    @previousColumnDelimiterPosition = 
                  @columnDelimiterPosition + 1
            SET   @columnDelimiterPosition = 
                  CHARINDEX(@columnDelimiter, 
                  @rowValues, 
                  @previousColumnDelimiterPosition)
         END
   
         SET    @previousColumnDelimiterPosition = 0
         SET    @previousRowDelimiterPosition = 
            @rowDelimiterPosition + 2
         SET    @rowDelimiterPosition = 
               CHARINDEX(@rowDelimiter, 
               @territories, 
               @previousRowDelimiterPosition)


   END

   -- return result set filtered by selected territories
   SELECT o.OrderDate, o.SalesOrderNumber, 
         o.TotalDue, o.SubTotal, o.TaxAmt,
         t.TerritoryName, o.Status, o.ShipDate, 
         o.AccountNumber
   FROM Sales.SalesOrderHeader o
         INNER JOIN @territoryList t ON 
         o.TerritoryID = t.ID

   SET NOCOUNT OFF

END
comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.