Introduction

We have all faced the problem while designing our applications using MS SQL Server 2005 where we want to send multiple rows needing to be modified (inserted or updated or deleted) to the database. We already know how to return a set of rows from the database to an application, but we did not have any features provided until SQL Server 2005 that would allow us to send multiple rows to the database.

I have tried to present the following two efficient approaches to work around this problem. They are:

1. Delimited String Approach - explained in this article
2. XML data approach - explained in part II of this article.

Delimited String Approach

In this approach we send a delimited string of data corresponding to each column of a table, to a Stored Procedure (SP) from the application layer. The SP should then have functionality to parse the delimited string to extract the data values and then modify the record.

Whenever parsing comes into the picture we only think about fetching values one by one and modifying the record in a loop, which in turn creates multiple calls to the database engine. As we all know that if we request the database at once, for this kind of processing, there will be an appreciable gain in the performance of the SP.

I have illustrated two implementations of the delimited string approach in the examples in subsequent sections.

XML Data Approach

In this approach we create an XML string at our application layer and send it to the SP. The SP should then have the functionality to parse the XML to extract the data elements and then modify the record. The advantage here is that we don't need to code for parsing the XML explicitly, as SQL Server 2005 can do this for us.

This approach will be covered in Part II of this article.

Delimited String Approach Implementation

For the delimited string approach, I have presented the following two implementations based on my experience:

1. Table valued function using Numbered List: This table valued function will create a numbered list and then using SUBSTRING and CHARINDEX inbuilt functions would convert the delimited string into a table.
2. Table valued function using recursive CTE (Common Table Expression): This table valued function also uses SUBSTRING and CHARINDEX inbuilt functions, along with a recursive CTE.

Table valued function using Number List

First I will explain the pieces of code, which form the building blocks of the final table valued function.
We need to create a number list using the CTE as below

;WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Number FROM L4)
SELECT * FROM Numbers

This CTE is creating the list of numbers from 1 to POWER(POWER(POWER(POWER(2, 2), 2), 2), 2), i.e. until 65536.

Now Consider the below code snippet, where @list and @delim variables have been assigned.

DECLARE
@list NVARCHAR(MAX), @delim NCHAR(1)
SELECT @list = 'aaa,bbbbb,cccc,dddd', @delim = ','
;WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Number FROM L4
)
SELECT 
@list List, 
SUBSTRING(@list, Number, CHARINDEX(@delim, @list + @delim, Number) - Number) AS Value, 
Number AS StartingFrom, 
CHARINDEX(@delim, @list + @delim, Number) AS DelimeterPosition 
FROM Numbers
WHERE Number <= CONVERT(INT, LEN(@list))
  AND SUBSTRING(@delim + @list, Number, 1) = @delim 

The SUBSTRING statement, cuts characters from @list starting from character position (1, 5, 11 and 16).

SUBSTRING(@list, Number, CHARINDEX(@delim, @list + @delim, Number) - Number)

The number of characters to be cut is decided by CHARINDEX which will return 4, 10, 15, 20 in each row, where it finds the delimiter character.

CHARINDEX(@delim, @list + @delim, Number) - Number

The above SELECT only works until the number of characters present in the @list variable due to the condition

Number <= CONVERT(INT, LEN(@list)) 

The duplicate values are filtered out from the output list by the "WHERE" condition created using the SUBSTRING function which will only return a value when it finds the delimiter

SUBSTRING(@delim + @list, Number, 1) = @delim

The output of the code snippet above would be:

Table valued function using Numbered List: Implementation

Now combining all the above explained pieces of SQL, we create our table valued function which will parse the string and return a table having two columns viz. ID and Data.

CREATE FUNCTION [dbo].[TableFormDelimitedString]
(
@paramNVARCHAR(MAX),
@delimeterNCHAR(1)
)
RETURNS @tmp TABLE 
(
IDINT IDENTITY (1, 1),
DataVarchar(MAX)
)
BEGIN

;WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
  Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Number FROM L4
)
INSERT INTO @tmp (Data)
SELECT LTRIM(RTRIM(CONVERT(NVARCHAR(4000),
SUBSTRING(@param, Number, 
CHARINDEX(@delimeter, @param + @delimeter, Number) - Number
)
))) AS Value
       FROM   Numbers
       WHERE  Number <= CONVERT(INT, LEN(@param))
         AND  SUBSTRING(@delimeter + @param, Number, 1) = @delimeter
RETURN 
END

Table valued function using Numbered List: Usage

So if we now invoke the above function like

SELECT * FROM [TableFormDelimitedString]('Andy:Roger:Thomas:Rob:Victor', ':')

We will obtain the following result set

Table valued function using recursive CTE

Here again I will first explain the pieces of code, which form the building blocks of the final table valued function.

As we know, in a recursive CTE, we have one anchor part and one recursive part. But if we create a CTE having only the anchor part it would look something like

DECLARE
@list NVARCHAR(MAX), @delim NCHAR(1)
SELECT @list = 'aaa,bbbbb,cccc,dddd', @delim = ','
;WITH CTETable (start, stop)  
AS  
( 
SELECT start = CONVERT(bigint, 1), stop = CHARINDEX(@delim, @list + @delim, 1)
)
SELECT @list List, LTRIM(RTRIM(SUBSTRING(@list, start, 
 CASE 
 WHEN stop > 0 
 THEN stop - start 
 ELSE 0 
 END
))) AS Data 
start AS StartingFrom, stop AS DelimiterPosition
FROM CTETable

The output of the SQL above will be like

Now by adding a recursive member to the above CTE, which iterates over the stop variable, the SQL looks like

DECLARE
@list NVARCHAR(MAX), @delim NCHAR(1)
SELECT @list = 'aaa,bbbbb,cccc,dddd', @delim = ','
;WITH CTETable (start, stop)  
AS  
( 
SELECT start = CONVERT(bigint, 1), stop = CHARINDEX(@delim, @list + @delim, 1)
UNION ALL-- added for recursive part of CTE
SELECT start = stop + 1, stop = CHARINDEX(@delim, @list + @delim, stop + 1) FROM CTETable WHERE stop > 0-- added for recursive part of CTE
)
SELECT @list List, LTRIM(RTRIM(SUBSTRING(@list, start, 
 CASE 
 WHEN stop > 0 
 THEN stop - start 
 ELSE 0 
 END
))) AS Data 
start AS StartingFrom, stop AS DelimiterPosition
FROM CTETable
WHERE stop > 0

And gives the following result set

Table valued function using recursive CTE: Implementation

Finally we create a table valued function from the above code blocks, which looks like

CREATE FUNCTION [dbo].[TableFormDelimitedStringWithoutNumberList] 
(
@listNVARCHAR(MAX),
@delimNCHAR(1) = ','
) 
RETURNS @tmp TABLE 
(
IDINT IDENTITY (1, 1),
DataVarchar(MAX)
)
BEGIN
;WITH CTETable (start, stop) 
AS 
(
SELECT start = CONVERT(bigint, 1),
stop = CHARINDEX(@delim, @list + @delim)
UNION ALL-- added for recursive part of CTE
SELECT start = stop + 1,
stop = CHARINDEX(@delim, @list + @delim, stop + 1)-- added for recursive part of CTE
FROM   CTETable
WHERE  stop > 0
)
INSERT INTO @tmp (Data)
SELECT LTRIM(RTRIM(SUBSTRING(@list, start, 
CASE 
WHEN stop > 0 
THEN stop - start 
ELSE 0 
END))) AS Data
FROM CTETable
WHERE stop > 0
RETURN 
END

Table valued function using recursive CTE: Usage

So if we now invoke the above function like

SELECT * FROM [TableFormDelimitedStringWithoutNumberList]('Andy:Roger:Thomas:Rob:Victor', ':')

We will obtain the following result set:

Why I like these two implementations is because the looping has been handled by the SQL server database engine itself, which would definitely be more efficient than explicit SQL looping code written by a developer.

Using the table valued function Implementations

As we know that the two table valued function implementations viz. TableFormDelimitedString and TableFormDelimitedStringWithoutNumberList explained above, return a table from a delimited string along with its position as ID, we will now see how to invoke them from a sample SP.

For the purpose of this illustration I have used the table below, wherein we need to insert multiple records sent by the application layer.

--Creating a base table
CREATE TABLE Emp(
IDINTIDENTITY (1, 1),
NameVARCHAR(50),
Salary  INT
);

To invoke these two functions, we create an SP which takes multiple delimited strings and a delimiter as input. This SP will call one of the two table valued functions, which would in turn convert the delimited string into a table.

In our sample SP we have employee names and their salaries in two different delimited strings, which need to be parsed. As the table valued functions described above, can transform a delimited string into a table with an ID column, we will use this ID column to join two different tables, created from the two different delimited strings i.e. employee names and salaries.

Using the table valued function Implementations: Sample SP Implementation

The sample SP, which takes multiple parameters and inserts the records into the base table (Emp), looks like

CREATE PROCEDURE InsertEmp1  
(
@sNameNVARCHAR(MAX),
@sSalaryNVARCHAR(MAX),
@delimeterCHAR (1)
)
AS
BEGIN
INSERT INTO Emp (Name, Salary)
SELECT NameList.Data, SalaryList.Data
FROM TableFormDelimitedString (@sName, @delimeter) NameList
INNER JOIN TableFormDelimitedString (@sSalary, @delimeter) SalaryList
ON NameList.ID = SalaryList.ID
RETURN 0
END

Using the table valued function Implementations: Sample SP Usage

Here I am passing two strings "'Andy:Roger:Thomas:Rob:Victor'", "'100:200:1000:500:50'" delimited by ":" to the SP, which will insert the corresponding records in to the target table.

EXEC InsertEmp1 'Andy:Roger:Thomas:Rob:Victor', '100:200:1000:500:50', ':'

We can see the result by selecting from the target table.
SELECT * FROM Emp



ID
Name
Salary


1
Andy
100


2
Roger
200


3
Thomas
1000


4
Rob
500


5
Victor
50


Summary
In this article we discussed how to parse a delimited string in SQL Server 2005, using the two table valued function implementations, without writing an explicit loop statement. We Also discussed CTEs (Common table Expression), one of the nicest (albeit a little complex) feature of the SQL Server 2005. Hope, this can help you get around the multiple row send problem while designing your applications using SQL server 2005.
Watch this space for part II of this article, Sending multiple rows to the Database from an Application: Part II, where I will to explain the XML Data Approach using XML data and XQuery functions of SQL Server 2005.
Happy coding