Handy way of splitting comma separated values in T-SQL without using a function

-
I came across this situation wherein a need to split a comma separated value and insert it to the database, one by one. here's the solution to the problem.


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
CREATE PROCEDURE ppm_InsertDealerContactsReportXref
 -- Add the parameters for the stored procedure here
 @dlrcxIddlrc INT = 0
 ,@dlrcxIdcl VARCHAR(300)
AS
BEGIN
 SET NOCOUNT ON;

 DELETE
 FROM dbo.rpm_DealerContactsReportXref
 WHERE dlrcxIddlrc = @dlrcxIddlrc

 DECLARE @DealerContactsReportXref TABLE (
  dlrcxIddlrc INT
  ,dlrcxIdcl VARCHAR(100)
  )

 INSERT INTO @DealerContactsReportXref (
  dlrcxIddlrc
  ,dlrcxIdcl
  )
 VALUES (
  @dlrcxIddlrc
  ,@dlrcxIdcl
  )

 INSERT INTO rpm_DealerContactsReportXref (
  dlrcxIddlrc
  ,dlrcxIdcl
  )
 SELECT dlrcxIddlrc
  ,LTRIM(RTRIM(m.n.value('.[1]', 'varchar(8000)'))) AS ids
 FROM (
  SELECT dlrcxIddlrc
   ,CAST('' + REPLACE(dlrcxIdcl, ',', '
') + '' AS XML) AS x
FROM @DealerContactsReportXref ) t CROSS APPLY x.nodes('/XMLRoot/RowData') m(n) SELECT @@ROWCOUNT END GO

Comments

Post a Comment

Popular posts from this blog

Serializing JSON string to ExpandoObject

-
In some case you want to create and Object from JSON, a great way of doing this to deserialize this JSON to an ExpandoObject. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 /// <summary> /// Convert Json String to an Expando Object /// </summary> /// <param name="that">Json string to convert</param> /// <returns>Expando Object</returns> public static ExpandoObject ToExpando ( this string that) { ExpandoObject result; try { result = JsonConvert.DeserializeObject<ExpandoObject>(that, new ExpandoObjectConverter()); } catch (Exception e) { throw e; } return result; } Here we use JsonConvert.DeserializeObject, you need to add reference to NewtonSoft.Json see below. 1 2 3 using Newtonsoft.Json ; using Newtonsoft.Json.Converters ; using Newtonsoft.Json.Linq ;
Read more

Adding an Obsolete Attribute to Class Methods in C#

-
As a developer we normally maintain our own framework, our own abstraction of things, be it database wrappers, our own implementations of persistence managers, in some cases we figure out a better way or new way of doing things so we decide to include it in the framework, how do we now tell other developers to use this instead of the old one? The answer is the Obsolete Attribute. 1 2 3 4 5 6 7 8 9 [Obsolete("ExpandoObject.CreateObject is deprecated, please use NameValueCollection.CreateObject instead.", true)] public static void CreateObject ( this ExpandoObject that, NameValueCollection querystring) { querystring.AllKeys.ToList().ForEach(x => { if (!that.ToDictionary(y => y.Key).ContainsKey(x)) ((IDictionary< string , object >)that)[x] = querystring[x]; }); } We simply add this attribute on top of the method or class and specify our message. BTW, the Boolean parameter if set to True, will throw and exceptio...
Read more