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