Often, the correct answer to any SQL Server problem is just two words – “It depends!” And the fact is that the more I work with SQL Server, the importance of these two words cements itself into the foundations of my thinking.
Let’s take an example which I came across recently, and which prompted this post.
The Challenge
For one of the ad-hoc training assignments that I was supervising, the design was such that there would be a comma-separated list as input to one of the stored procedures. These values had to be inserted as records into a table. The application/script was to be run on SQL Server 2008 R2 and above only.
If we talk about implementation, there are a couple of different ways in which it can be done with approaches ranging from using a simple WHILE loop to using PATINDEX. While all participants came up with these, I decided to spice things up a bit. The challenge I presented to them was:
Generate the statement to insert values from a comma-separated string to a table in a single T-SQL statement.
The Answer
The answer to this problem is in thinking simple. Given that the script needs to run only on SQL Server 2008 R2 and above, we can convert the comma-separated string into row constructors for use with the VALUES clause. This can be done in a single T-SQL statement by using the REPLACE function as shown below:
USE tempdb;
GO
BEGIN
SET NOCOUNT ON;
--Safety Check
IF OBJECT_ID(N'#productNames', N'U') IS NOT NULL
BEGIN
DROP TABLE #productNames;
END;
CREATE TABLE #productNames(Product VARCHAR(25));
--Input Parameter
DECLARE @parameterNames NVARCHAR(MAX)= N'Microsoft,SQL Server,Windows,Office,Sharepoint,BizTalk';
--Query Statement
DECLARE @stmnt NVARCHAR(1000);
--Build the query
--Key is to use the REPLACE function here
SELECT @stmnt = N'INSERT INTO #productNames (Product) VALUES ' + N'(''' + REPLACE(@parameterNames, ',', '''),(''') + N''')';
--Check the query
SELECT @stmnt AS QueryStatement;
--Execute the query
EXEC sp_executesql
@stmnt;
--Check the query result
SELECT pn.Product
FROM #productNames AS pn;
--Cleanup
IF OBJECT_ID(N'#productNames', N'U') IS NOT NULL
BEGIN
DROP TABLE #productNames;
END;
END;
GO
/***********************************RESULT(formatted for sake of readability)***********************************/
/*QueryStatement
------------------------------------
INSERT INTO #productNames (Product)VALUES ('Microsoft'),('SQL Server'),('Windows'),('Office'),('Sharepoint'),('BizTalk')
Product
-----------
Microsoft
SQL Server
Windows
Office
Sharepoint
BizTalk
*/
Needless to say this approach comes with all the restrictions of row constructor, the prime one being that the number of records is restricted to 1000. The script above can easily be modified to take this into account as well.
Conclusion
In conclusion, all I would say is there are many different ways to approach a problem in SQL Server. The correct approach is one that works for the situation and problem at hand.
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 
Nice variant for solving the classic unpivot (horizontal to vertical) problem.
But this is impossible to use in functions.
Thus I will stick to this classic solution:
select split.a.value(‘.’,’varchar(1000)’) as item
from (select cast(”+replace(‘Microsoft,SQL Server,Windows,Office,Sharepoint,BizTalk’,’,’,”)+” as xml) csl) t
cross apply csl.nodes(‘/M’) split(a)
LikeLike
What an amazing security hole, ripe for a SQL Injection attack.
See what happens when you change @parameterNames to this:
DECLARE @parameterNames NVARCHAR(MAX) = N’Microsoft,SQL Server,Windows”);CREATE LOGIN EvilPerson WITH PASSWORD=”xx*yy*zz*11*22*33”;ALTER SERVER ROLE sysadmin ADD MEMBER EvilPerson–,Office,Sharepoint,BizTalk’ ;
Congratulations! You now have a brand new sysadmin on the server, who can do anything they like.
Do not EVER put code this this into production.
LikeLike
Instead of using dynamic SQL, which is vulnerable to a SQL Injection attack, try this:
USE tempdb ;
GO
BEGIN
SET NOCOUNT ON ;
–Safety Check
IF OBJECT_ID(N’#productNames’, N’U’) IS NOT NULL
BEGIN
DROP TABLE #productNames ;
END
CREATE TABLE #productNames ( Product VARCHAR(25) ) ;
–Input Parameter
DECLARE @parameterNames NVARCHAR(MAX) = N’Microsoft,SQL Server,Windows,Office,Sharepoint,BizTalk’ ;
WITH — start generating tally numbers
CTE_Tally_1 AS ( SELECT CAST(1 as [int]) as [value] UNION ALL SELECT CAST(1 as [int]) as [value] ),
CTE_Tally_2 AS ( SELECT [a].[value] FROM [CTE_Tally_1] [a], [CTE_Tally_1] [b], [CTE_Tally_1] [c] ),
— present tally numbers
CTE_Tally_Numbers AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as [value] FROM [CTE_Tally_2] [a], [CTE_Tally_2] [b], [CTE_Tally_2] [c], [CTE_Tally_2] [d], [CTE_Tally_2] [e] ),
— break string into individual characters, present as a table
CTE_Characters AS ( SELECT [value],
SUBSTRING(@parameterNames, [value], 1) as [character]
FROM CTE_Tally_Numbers
WHERE SUBSTRING(@parameterNames, [value], 1) N”
),
— parse out the breaks ‘,’
CTE_Breaks AS ( SELECT ROW_NUMBER() OVER (ORDER BY [value]) as [num],
[value]
FROM CTE_Characters
WHERE [character] = ‘,’
),
— calculate the start and end of each substring
CTE_Start_End AS ( SELECT ISNULL([start].[value], 0) + 1 as [start],
ISNULL([end].[value], 9999999) – 1 as [end]
FROM [CTE_Breaks] [end] FULL OUTER JOIN [CTE_Breaks] [start] ON [start].[num] = [end].[num] – 1
)
INSERT INTO #productNames (Product)
— present back each value as a row in a table
SELECT SUBSTRING(@parameterNames, [start], [end]-[start] + 1)
FROM CTE_Start_End
ORDER BY [start]
–Check the query result
SELECT pn.Product
FROM #productNames AS pn ;
–Cleanup
IF OBJECT_ID(N’#productNames’, N’U’) IS NOT NULL
BEGIN
DROP TABLE #productNames ;
END
END
GO
/***********************************
RESULT
***********************************/
/*
Product
———–
Microsoft
SQL Server
Windows
Office
Sharepoint
BizTalk
*/
LikeLike
@Marc: Completely agree with you. The method shown is absolutely not “production-grade”, but just wanted to present to the attendees a different way to look at things.
Your method of using tally numbers/table is a good one! Just another proof that the goal can be achieved in T-SQL via multiple different approaches.
Thank-you for sharing the script and providing your comments.
LikeLike
Nakul, you present this as the “correct” answer in the context of an “ad-hoc training assignment” that you were supervising.
It’s only after you are called out in a comment that you acknowledge that your solution is not “production grade”.
How is something correct, but not production grade?
The ONLY code that should ever be posted as a “correct” solution is one that is “production grade”.
Your “correct” solution is open to a SQL injection attack, putting the entire business at risk.
LikeLike
Marc,
This injection will only work if the user running the code has the necessary privileges.via a server role.
A normal user running this injection will simply generate an error.
I tend to give minimal privileges to users running application code.
BTW, your code returns no rows when the parameter value does not contain at least one comma.
In fact, my code is getting posted incorrectly and thus produces incorrect results as well.
Will have a go at fixing it.
LikeLike
dishdy, the ability to provision a new user and make them a sysadmin is an extreme case.
The injected string could just as easily drop tables, delete rows, insert bad data or just run a tight loop for a Denial of Service attack.
You were right about not returning any rows if there was no comma in the parameter. Here’s the update.
USE tempdb ;
GO
BEGIN
SET NOCOUNT ON ;
–Safety Check
IF OBJECT_ID(N’#productNames’, N’U’) IS NOT NULL
BEGIN
DROP TABLE #productNames ;
END
CREATE TABLE #productNames ( Product VARCHAR(25) ) ;
–Input Parameter
DECLARE @parameterNames NVARCHAR(MAX) = N’Microsoft,SQL Server,Windows,Office,Sharepoint,BizTalk’ ;
WITH — start generating tally numbers
CTE_Tally_1 AS ( SELECT CAST(1 as [int]) as [value] UNION ALL SELECT CAST(1 as [int]) as [value] ),
CTE_Tally_2 AS ( SELECT [a].[value] FROM [CTE_Tally_1] [a], [CTE_Tally_1] [b], [CTE_Tally_1] [c] ),
— present tally numbers
CTE_Tally_Numbers AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as [value] FROM [CTE_Tally_2] [a], [CTE_Tally_2] [b], [CTE_Tally_2] [c], [CTE_Tally_2] [d], [CTE_Tally_2] [e] ),
— break string into individual characters, present as a table
CTE_Characters AS ( SELECT [value],
SUBSTRING(@parameterNames, [value], 1) as [character]
FROM CTE_Tally_Numbers
WHERE SUBSTRING(@parameterNames, [value], 1) N”
),
— parse out the breaks ‘,’
CTE_Breaks AS ( SELECT ROW_NUMBER() OVER (ORDER BY [value]) as [num],
[value]
FROM CTE_Characters
WHERE [character] = ‘,’
),
— calculate the start and end of each substring
CTE_Start_End AS ( SELECT ISNULL([start].[value], 0) + 1 as [start],
ISNULL([end].[value], 9999999) – 1 as [end]
FROM [CTE_Breaks] [end] FULL OUTER JOIN [CTE_Breaks] [start] ON [start].[num] = [end].[num] – 1
)
–INSERT INTO #productNames (Product)
— present back each value as a row in a table
SELECT SUBSTRING(@parameterNames, [start], [end]-[start] + 1)
FROM CTE_Start_End
WHERE EXISTS (SELECT * FROM CTE_Start_End)
UNION ALL
SELECT TOP 1
@parameterNames
FROM CTE_Tally_1
WHERE NOT EXISTS (SELECT * FROM CTE_Start_End)
–Check the query result
SELECT pn.Product
FROM #productNames AS pn ;
–Cleanup
IF OBJECT_ID(N’#productNames’, N’U’) IS NOT NULL
BEGIN
DROP TABLE #productNames ;
END
END
GO
/***********************************
RESULT
***********************************/
/*
Product
———–
Microsoft
SQL Server
Windows
Office
Sharepoint
BizTalk
*/
LikeLike