Transact-SQL Barcode Font Encoder Formula Tutorial

This Transact-SQL (TSQL) barcode font tutorial explains to database developers and users how to encode data for barcodes in Microsoft SQL Server using TSQL font formulas.

Compatibility

Download Demo The demo version includes only the Code 39 symbology for proof of concept.

Transact-SQL Barcode Font Formulas Tutorial Overview

This demonstration uses IDAutomation's TSQL function in MS SQL Server to encode data in a field for Report Builder. To display the barcode in Report Builder, select the barcode font on the data field. The example uses the IDAutomation Code 128B font formula to convert an Employee ID field into a field prepared for the barcode font.

Applications and Components Used for this Tutorial

Note: Use of this SQL Font Formula, requires a Developer License or above. This font encoder is supplied with the purchase of a Developer's License for the Code 128, Code 39, or Interleaved 2 of 5 Font Packages.

Transact-SQL Barcode Font Formulas Tutorial

  1. Download and install the IDAutomation Barcode Fonts. The example uses the Code 128 Font Package to generate Code 128 barcodes. Locate the TSQL Font Formulas in the Licensed version of the separate Developer Tools ZIP file included with the purchase for Code 128, Code 39, and Interleaved 2 of 5.
  2. Run MS SQL Server and connect to the database.
  3. Navigate to Programmability - Functions - Scalar-valued Function and right-click the folder. Select New Scalar-valued Function.
    New Scalar-valued Function
  4. Erase the contents of the SQLQuery.sql file and then paste the IDAutomation font formula (copy the code from the IDAutomation_Code128B.sql file) into the query file.
  5. Select Execute. This operation stores the function in the Scalar-valued Functions list.
    Store the function in the Scalar-valued Functions list
    NOTE: To view the function in the Scalar-valued Functions folder, right-click the folder and choose Refresh. Refresh the Scalar Function
  6. Create a query; select the New Query button.
    Create a Query
  7. Select the fields to use from the table in the database.
    Query Table
    Results:
    Query Results
  8. Use the formula format DatabaseName.dbo.FunctionName([Field]) As FieldName to encode the field for the barcode.
    1. DatabaseName is the name of the database.
    2. FunctionName is the name of the IDAutomation function.
    3. Field is the DataToEncode.
    4. FieldName is the name of the column.
  9. To save the results into a new table, use INTO and the name of the new table, such as INTO NewTable.
    Encode the Fields
  10. Select Execute.
  11. The new table with the encoded field is added to the database. If the table is not visible, right-click the Tables folder, and select Refresh.
    The new table with the encoded field is added to the database.
  12. Right-click the new table and choose Select Top 1000 Rows to query the results. The BarcodeID field displays the encoded data.
    right-click the new table and choose Select Top 1000 Rows. The BarcodeID field displays the encoded data
  13. Export the encoded data as a data source into an application, such as Report Builder. To display the barcode in an application, highlight the encoded data or encoded data field, and then choose the IDAutomationC128M barcode font.

Transact SQL Barcode Font Formulas

TSQL Functions for Barcode Fonts
Barcode Type Barcode Function Methods and Notes Required Font
Code-128
(Auto Mode)		 dbo.Code128 (DataToEncode, ApplyTilde)

Example:
dbo.Code128('123456789012',0)		 IDAutomationC128
Code-128
(Manual Mode)		 IDAutomation recommends using Auto Mode for most situations.
dbo.Code128A (DataToEncode)
dbo.Code128B (DataToEncode)
dbo.Code128C (DataToEncode)

Example:
dbo.Code128b('12345678')		 IDAutomationC128
Code-39 dbo.Code39 (DataToEncode)
dbo.Code39Mod43 (DataToEncode)

Example:
dbo.Code39Mod43('12345678')		 IDAutomationC39
GS1-128 dbo.Code128 (DataToEncode, 1)
GS1-128 is enabled in Code 128 Auto by setting ApplyTilde to True.

Example:
dbo.Code128('(12)3456789012',1)		 IDAutomationC128
Interleaved 2 of 5 dbo.I2of5 (DataToEncode)
dbo.I2of5Mod10 (DataToEncode)

Example:
dbo.I2of5Mod10('123456789')		 IDAutomationI25

Support Methods

Popular Forum Post Resolutions: