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.
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 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.
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,