SQL-CLR: User Defined TVF - Barcode Parser

So, I created another SQL-CLR. This time it is a User Defined Function that is used to parse barcodes.

When setting this up, I added two attributes

• FillRowMethodName - which is used to populate a table's row. The setup for this function, line 132 - 156, must accept an object as it's first value and then output fields that are used to populate the row.
• TableDefinition - what the table that I want to return should include. One of the problems I ran into was that I was originally using varchar as my table values, but I would get this error: "Cannot use 'varchar' column in the result table of a streaming user-defined function".

For the intializing function, BarCodeParsercs, I need to return an IEnumerable value. Each value that is return in this function will end up as a row. In my case I just want one row, so I just created a string array with my single value.

This will end up calling the FillRow function to populate the row, for each value in the IEnumerable. In this case I call the GetData function to parse out my barcode and return a string array of values I needed to populate each of the table fields.

Now to deploy, you will need to first set your database table as TRUSTWORTHY

Note: To help debug the deployment script, copy the auto-generated deployment sql script, which can be found in the \bin\Debug and run it in SSMS, turned on "SQLCMD Mode" and executed the statement.

Code Snippet

1. using System;
2. using System.Data;
3. using System.Data.SqlClient;
4. using System.Data.SqlTypes;
5. using Microsoft.SqlServer.Server;
6. using System.Collections;
7.  
8. public partial class UserDefinedFunctions
9. {
10.     [Microsoft.SqlServer.Server.SqlFunction(
11.         FillRowMethodName="FillRow",
12.         TableDefinition = "BarcodeType nvarchar(50) NULL, " +
13.                         "PO nvarchar(50) NULL, " +
14.                         "PartNumber nvarchar(50) NULL, " +
15.                         "Qty nvarchar(50) NULL, " +
16.                         "DateCode nvarchar(50) NULL, " +
17.                         "SupplierName nvarchar(50) NULL, " +
18.                         "SupplierSite nvarchar(50) NULL, " +
19.                         "Date nvarchar(50) NULL, " +
20.                         "Weight nvarchar(50) NULL")]
21.     public static IEnumerable BarCodeParsercs(SqlString barcode)
22.     {
23.         return new String[]{barcode.Value};
24.     }
25.  
26.     private static IEnumerable GetData(SqlString barcode)
27.     {
29.         string BarCodeType = "";
30.         string PO = "";
31.         string PartNumber = "";
32.         string Qty = "";
33.         string DateCode = "";
34.         string SupplierName = "";
35.         string SupplierSite = "";
36.         string Date = "";
37.         string Weight = "";
38.  
39.         string Barcode = barcode.Value;
40.  
41.         BarCodeType = Barcode.Substring(0, 3);
42.         switch (BarCodeType)
43.         {
44.             case "]d2":
45.                 {
46.                     BarCodeType = "]d2";
47.                     Barcode = Barcode.Substring(3);
48.                     break;
49.                 }
50.             case "]C0":
51.                 {
52.                     BarCodeType = "]C0";
53.                     Barcode = Barcode.Substring(3);
54.                     break;
55.                 }
56.         }
57.  
58.         string[] sections = Barcode.Split('~');
59.  
60.         foreach (string section in sections)
61.         {
62.             string ThreeDigitCode = "";
63.             string TwoDigitCode = "";
64.             bool FoundMatch = false;
65.  
66.             if (section.Length > 3)
67.             {
68.                 ThreeDigitCode = section.Substring(0, 3);
69.             }
70.             if (section.Length > 2)
71.             {
72.                 TwoDigitCode = section.Substring(0, 2);
73.             }
74.  
75.             switch (ThreeDigitCode)
76.             {
77.                 case "241":
78.                     {
79.                         PartNumber = section.Substring(3);
80.                         FoundMatch = true;
81.                         break;
82.                     }
83.                 case "400":
84.                     {
85.                         PO = section.Substring(3);
86.                         FoundMatch = true;
87.                         break;
88.                     }
89.             }
90.             if (FoundMatch.Equals(false))
91.             {
92.                 switch (TwoDigitCode)
93.                 {
94.                     case "96":
95.                         {
96.                             Qty = section.Substring(2);
97.                             break;
98.                         }
99.                     case "93":
100.                         {
101.                             DateCode = section.Substring(2);
102.                             break;
103.                         }
104.                     case "91":
105.                         {
106.                             SupplierName = section.Substring(2);
107.                             break;
108.                         }
109.                     case "92":
110.                         {
111.                             SupplierSite = section.Substring(2);
112.                             break;
113.                         }
114.                     case "94":
115.                         {
116.                             Date = section.Substring(2);
117.                             break;
118.                         }
119.                     case "95":
120.                         {
121.                             Weight = section.Substring(2);
122.                             break;
123.                         }
124.                 }
125.             }
126.         }
127.  
128.         return new string[] { barcode.Value, BarCodeType, PO, PartNumber, Qty, DateCode, SupplierName, SupplierSite, Date, Weight };
129.     }
130.  
131.  
132.     public static void FillRow(Object obj,
133.                             out SqlString BarcodeType,
134.                             out SqlString PO,
135.                             out SqlString PartNumber,
136.                             out SqlString Qty,
137.                             out SqlString DateCode,
138.                             out SqlString SupplierName,
139.                             out SqlString SupplierSite,
140.                             out SqlString Date,
141.                             out SqlString Weight)
142.     {
143.         String barcode = (String)obj;
144.         String[] BarCode = (String[])GetData(barcode);
145.  
146.  
147.         BarcodeType = (SqlString)BarCode[1];
148.         PO = (SqlString)BarCode[2];
149.         PartNumber = (SqlString)BarCode[3];
150.         Qty = (SqlString)BarCode[4];
151.         DateCode = (SqlString)BarCode[5];
152.         SupplierName = (SqlString)BarCode[6];
153.         SupplierSite = (SqlString)BarCode[7];
154.         Date = (SqlString)BarCode[8];
155.         Weight = (SqlString)BarCode[9];
156.     }
157. };

Once the code has been succesfully deployed. I just need to call the function, in SSMS, like so:
SELECT* FROM [dbo].[BarCodeParsercs] (N']d2400PO_09928-1~2413000510_E~96400~9320100614-320008210~910278~9232~94')




Helpful Resources:

http://www.mssqltips.com/sqlservertip/2582/introduction-to-sql-server-clr-table-valued-functions/
http://www.mssqltips.com/sqlservertip/1986/sqlclr-function-to-return-free-space-for-all-drives-on-a-server/

Multi-Processor - Parallel SQL does not work well with SCOPE_IDENTITY()

Just something to be aware of, that was brought up at work. When working with parallel processing, the SCOPE_IDENTITY() might bring back the wrong id. This is true for SQL Server 2008 R2 and lower. This is suppose to be fix for SQL Server 2012.

http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value

http://blog.sqlauthority.com/2009/03/24/sql-server-2008-scope_identity-bug-with-multi-processor-parallel-plan-and-solution/

Mean while, use the OUTPUT to get the newly inserted value, if need be.

OLD*******************************

 INSERT INTO [Database1].[dbo].[Table1]

([Field1]
,[LDTS]
,[RS])
VALUES(@Field1, @LDTS, @RS)

SET @FieldID = SCOPE_IDENTITY()

NEW******************************

Declare @GetID Table (FieldID smallint)

INSERT INTO [Database1].[dbo].[Table1]

([Field1]
,[LDTS]
,[RS])
OUTPUT Inserted.FieldID into @GetID      
VALUES(@Field1, @LDTS, @RS)

Select Top 1  @FieldID = FieldID from @GetID