SQL Tricks: CSV To Table Rows And Vice Versa


I was looking for a one-line SQL statement to convert a comma-separated value list string to table rows and vice-versa and below is what I found.  I prefer not to put logic in my SQL code (I prefer to limit it with just the simple CRUD statements) but if no other choice then these two SQL tricks will come in handy.  You can try to execute this SQL query on any online SQL compiler you can find on the Internet, but I would recommend rextester’s sql compiler.

-- So if you have a comma separated list, for example a list of integers
DECLARE @MyListOfInts VARCHAR(255) = '123,456,789'

-- Use the following SQL trick to parse them to table rows
-- Basically you convert the CSV to XML first and use the nodes() method to shred the XML into relational data
SELECT List.Item.value('.', 'INT')
FROM
(
    SELECT CAST('<List><Item>' + REPLACE(@MyListOfInts,',','</Item><Item>') + '</Item></List>' AS XML) AS ListXML
) ListXMLTable
CROSS APPLY ListXMLTable.ListXML.nodes('/List/Item') List(Item)


-- And if you have values in table rows that you want to convert to CSV...
-- The following SQL trick will do it for you
-- Basically the inner SELECT query with unnamed column and with FOR XML PATH('') converts the 
--  table row values to XML without any tags and the SUBSTRING() function in the outer SELECT query
--  just removes the leading comma character
SELECT SUBSTRING
(
    (
        SELECT ',' + CAST(List.Item AS VARCHAR(MAX))
        FROM
        (
            SELECT 123 AS Item UNION SELECT 456 UNION SELECT 789
        ) List
        ORDER BY List.Item
        FOR XML PATH('')
    )
    ,2
    ,1000
)

Advertisements

SQL Server: Hiding your prod data and built-in function STUFF()

If you need to use production data for your testing and don’t want to expose any sensitive data on your development environment, you should look at obfuscating them.  This article shows the common obfuscation methods in use: character scrambling, repeating character masking, numeric variance, nulling, artificial data generation, truncating, encoding, and aggregating.

Also, if you are looking for a function in SQL Server that pretty much does like the function REPLACE() but only replaces one substring in a specific position and length, then STUFF() might be the one.

Nested Transactions in SQL Server

If you have a stored proc that executes a bunch of SQL statements inside a transaction because they are all meant to be executed as one atomic transaction and needs to be executed as quick as possible to avoid blocking too long others who want to call this stored proc, then you need to make sure this stored proc is not nested in another transaction.  Because nested transactions in SQL server is different than the nesting concept that you might have been accustomed to in a programming language, say in C#.

-- Say for example you need to write to a field in a table
CREATE TABLE [dbo].[TestTable](
	[TestField1] [nchar](10) NULL
) ON [PRIMARY]
GO

-- And it needs to be in a transaction, so you created a stored proc below
CREATE PROCEDURE [dbo].[usp_TestInnerTrans] 
AS
BEGIN
	BEGIN TRANSACTION
		INSERT INTO [dbo].[TestTable] ([TestField1]) VALUES ('Value1')
	COMMIT TRANSACTION
END
GO

-- When you call this stored proc directly, say using EXEC, 
--  you can get at most a 1 sec exec time

-- Now what if you have another stored proc that calls usp_TestInnerTrans 
--  nested inside another transaction

-- Below is that other stored proc and let's say it takes 10 secs to finish 
--  and commit the outer transaction
CREATE PROCEDURE [dbo].[usp_TestOuterTrans] 
AS
BEGIN
	BEGIN TRANSACTION
	EXEC usp_TestInnerTrans
	WAITFOR DELAY '00:00:10'
	COMMIT TRANSACTION
END
GO

-- What happens is that any other process that calls usp_TestInnerTrans 
--  will block and will wait until usp_TestOuterTrans finishes, 
--  which is 10 secs and not 1 sec.

As you can see inner transactions are ignored and the outermost transaction becomes the only one true transaction.  SQL Server Transaction Locking and Row Versioning Guide has a section on nested transactions that explains it better.  It's a great article so I recommend reading it from start to finish.

SQL Server: My Quick and Dirty Way of Debugging Stored Procedure

I use the below SQL script to debug a stored procedure in chunks, replacing or adding into it the rest of the stored procedure code until every code checks out fine.  Since I use transactions, changes are temporary and it rolls back the transaction at the end.  I also used the error handling in SQL Server to catch and print the error line, error number and error message.

BEGIN TRANSACTION
BEGIN TRY
	--
	-- your SQL code here
	--
END TRY
BEGIN CATCH
	PRINT 'Error at line # ' + CAST(ERROR_LINE() AS VARCHAR(MAX)) 
		+ ': ' + CAST(ERROR_NUMBER() AS VARCHAR(MAX)) 
		+ ' - ' + ERROR_MESSAGE()
END CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION