Technical Article

Another way to get a table list from a string

,

the default delimiter is comma and the default list type is sysname.

You can pass whatever delimiter you want and ask for any datatype list.

Here is a sample of its use:

EXEC Usp_GetTableList @strDataSet='01/24/1965,1900-01-01 23:15:00.000,03/21/2009,1965,03/02/1975'

,@Rt='DATETIME'

EXEC Usp_GetTableList @strDataSet='$45,50.639,60.1965,123456.9'

,@Rt='MONEY'

--,@delimiter ='.'

EXEC Usp_GetTableList @strDataSet='45,50.639,60.19651234569'

,@Rt='NUMERIC(10,6)'

Enjoy it !

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO-- =============================================
-- Author: Bernabé Díaz
-- Create date: 04/08/2010
-- Description: To return a list from an string
-- =============================================
CREATE PROCEDURE Usp_GetTableList 
@strDataSet AS VARCHAR(max) 
,@delimiter AS VARCHAR(10)=',' 
,@Rt AS VARCHAR(128)='SYSNAME'
AS
BEGIN 
SET NOCOUNT ON;
DECLARE @STR VARCHAR(MAX);
SET @strDataSet=ISNULL(@strDataSet,'');
SET @STR=
' DECLARE @xml AS XML;'+
' SET @xml = CAST((''<X>''+REPLACE('''+@strDataSet+''','''+@delimiter+''' ,''</X><X>'')+''</X>'') AS XML);'+
' SELECT N.value(''.'', '''+@Rt+''') AS x FROM @xml.nodes(''X'') AS MyTable(N);'+
'
EXEC(@STR)
END

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating