Transact-SQL Barcode Font Encoder Formula Tutorial

This Transact-SQL (TSQL) barcode font tutorial provides a walk-through of steps for formatting data for barcodes in Microsoft SQL Server using TSQL font formulas. Once the font formula formats the data in MS SQL Server, barcode fonts can be applied to the formatted data source in applications such as Report Builder for SSRS.

Compatibility

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

Transact-SQL Barcode Font Formulas Tutorial Overview

Microsoft SQL Server database fields can be formatted for barcode fonts using IDAutomation Transact-SQL barcode font formulas. This tutorial will demonstrate how to use the barcode font formula in Microsoft SQL Server by calling the function in a query and storing the formatted data in a table. 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 Purchase of 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 purchased IDAutomation Barcode Fonts. This example uses the Code 128 Font Package to generate Code 128 barcodes. The TSQL Font Formulas are located in the Licensed version of the separate Developer Tools Zip file included with 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 paste the IDAutomation font formula (copy the code from the IDAutomation_Code128B.sql file) into the query file.
  5. Select Execute. This will store 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. To create a query, select the New Query button.
    Create a Query
  7. Select the fields to use from the table in the database. This tutorial will convert [Employee ID] from the [Employee] table to a field formatted for the barcode by applying the font formula.
    Query Table
    Results:
    Query Results
  8. To encode the field for the barcode, use the formula format DatabaseName.dbo.FunctionName([Field]) As FieldName where:
    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.

  12. To query the results, right-click the new table and choose Select Top 1000 Rows. The BarcodeID field displays the encoded data.

  13. The encoded data stored in the new table can be used as a data source in Report Builder and other applications. To create the barcode, apply the IDAutomationC128M barcode font to the encoded data. 

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: