Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Jano Petras,
Many times there is a need from the business logic layer to pass a list of items into stored procedure . As T-SQL has no concept of an array, the delimited strings are way to go. Most likely the required argument will be an "array" of integers (IDs), but it can be other data, or multidimensional array.
This function parses up to 9-dimensions delimited string data and generates a temporary table with the inidividual records.
The returned table always had 9 columns (named Field1, Field2, ... FIeld9), even if only 1 is used (the rest is populated with NULLs, therefore should not add much of the memory overhead).
Some example usages for example:
DECLARE @delimitedData VARCHAR(MAX)SET @delimitedData = '1023|Count of attempts|201045|Possible hacking|122010|Abandoned sessions|32312021|Hacked URLs|211'
CREATE TABLE #Array (ID INT PRIMARY KEY, [Description] VARCHAR(255), [Value] INT)
INSERT INTO #Array (ID, [Description], [Value])SELECT Field1, Field2, Field3 FROM dbo.ufn_ParseDelimitedString(@delimitedData, '|', char(13)+char(10))
SELECT * FROM #Array
DROP TABLE #Array
OUTPUT from this is:
ID Description Value==============================================1023 Count of attempts 201045 Possible hacking 122010 Abandoned sessions 32312021 Hacked URLs 211
The function also handles missing (NULL) values:
DECLARE @delimitedData VARCHAR(MAX)SET @delimitedData = '1023|Count of attempts|1045||122010|Abandoned sessions|32312021|Hacked URLs|211'
OUTPUT IS:ID Description Value==============================================1023 Count of attempts NULL1045 NULL 122010 Abandoned sessions 32312021 Hacked URLs 211
how would you represent an array of 1020 floats in record
Array in SQL server
Hresult: 0x80004005 Description: