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 