How to create barcodes using fonts with T-SQL (Transact SQL)?


Transact-SQL (T-SQL)

Transact-SQL (T-SQL) is Microsoft's extension of the SQL (Structured Query Language) for interacting with the SQL Server relational database. Application developers and database administrators can communicate with SQL Server by sending T-SQL statements to the server. T-SQL statements include SELECT statements, selecting columns, update tables, deleting rows, transaction control, restricting rows, specifying search conditions and error handling. Reporting tools such Microsoft SQL Server Reporting Services (SSRS) also depend on T-SQL to manipulate data in tables to formulate reports.

T-SQL Barcode Functions

ConnectCode Barcode Fonts and Software comes bundled with T-SQL Barcode Functions (Scalar Functions) to help SQL Server and SSRS developers generate barcodes using fonts. The functions perform the tasks of validating the input data, adding check characters and the necessary start/stop/control characters. The output characters can then be applied with a barcode font to create a compliant barcode that meets the strictest requirements of the auto-id industry,

How to generate a Code 128 barcode in SQL Server using T-SQL?

This tutorial illustrates the use of ConnectCode’s T-SQL Barcode Functions and high-quality barcode fonts to generate a Code 128 barcode. Other barcodes such as Code 39, GS1 128, EAN13, UPCA, I2of5, ITF14 and many others are also supported. Please check out the reference section at the end of this tutorial on how to create other barcodes using T-SQL.

Prerequisites

  • ConnectCode Barcode Fonts and Software package is installed
  • SQL Server 2012 (or later versions)
  • AdventureWorks sample database (or your database)
  • SQL Server Management Studio
1. Launch SQL Server Management Studio and connect to SQL Server with the necessary credentials.



2. Expand the Object Explorer and look for “Scalar-valued Functions” in your database. In our case, we are using the AdventureWorks database, the expansion of Object Explorer works out to be the following:

DESKTOP-PM4DFQ0\SQLEXPRESS->AdventureWorks2017->Programmability->Functions->Scalar-valued Functions

Right click on “Scalar-valued Functions” and select “Scalar-valued Function” in the context menu to create a new T-SQL function. A sample template function will be opened in the SQL Server Management Studio editor.



3. We will need to copy the predefined T-SQL barcode functions, available in ConnectCode Barcode Fonts and Software package, to the “Scalar-valued Function” editor. If you have installed the ConnectCode package in the default folder, the T-SQL barcode functions are available in the “C:\Program Files(x86)\ConnectCode\Resource\TSQL” folder. In this tutorial, we are going to create a Code 128 barcode. The file we are using is “Code128Auto.sql” (Code 128 barcode with auto detection subset). You can open the file with Notepad, copy the contents and paste it into the “Scalar-valued Function” editor. This is illustrated in the screenshot below. Click on the Execute button and you should see the function created successfully.

Note - If you are using the msix style installer for Windows 11, the "TSQL" folder is in the "Resource.zip" file.



The following function is created: 

    
	dbo. EncodeCode128Auto ( @InputData nvarchar(255))


The function takes in a single input data parameter. For some barcodes such as Code 39, the function supports additional parameters for specifying a check digit (1 for adding a check digit, 0 for no check digit). 

    
	dbo.EncodeCode39( @InputData nvarchar(255), @CheckDigit int )


  4. Next, you can test out the function with the following query. 

    
	select dbo.EncodeCode128Auto(ProductID),NAME 
		from AdventureWorks2017.Production.Product		
	GO


The output of the query is shown below. The query uses the “EncodeCode128Auto” function to encode the ProductID, adding the necessary check and start/stop characters. The return output characters of the first column can be applied with the Code 128 barcode font to display a standards compliant barcode font. For example, if you are creating a report in SSRS (SQL Server Reporting Service), you can generate a Code 128 barcode by applying the “CCode128_S3” (ConnectCode128_S3.ttf) barcode font with a font size of 24 in the first column.



List of T-SQL Barcode Functions and Fonts

Code39
    
	dbo.EncodeCode39( @InputData nvarchar(255), @CheckDigit int )


@CheckDigit - 1 for Check Digit, 0 for no Check Digit

Fonts

https://barcoderesource.com/code39_barcodefont.html

Code39ASCII
    
	dbo.EncodeCode39ASCII( @InputData nvarchar(255), @CheckDigit int )


@CheckDigit - 1 for Check Digit, 0 for no Check Digit

Fonts

https://barcoderesource.com/code39_barcodefont.html

Code128A
    
	dbo.EncodeCode128A( @InputData nvarchar(255))


Fonts

https://barcoderesource.com/code128_barcodefont.html

Code128Auto
    
	dbo.EncodeCode128Auto( @InputData nvarchar(255) )


Fonts

https://barcoderesource.com/code128_barcodefont.html

Code128B
    
	dbo.EncodeCode128B( @InputData nvarchar(255))


Fonts

https://barcoderesource.com/code128_barcodefont.html

Code128C
    
	dbo.EncodeCode128C( @InputData nvarchar(255) )


Fonts

https://barcoderesource.com/code128_barcodefont.html

EAN8
    
	dbo.EncodeEAN8( @InputData nvarchar(255) , @Hr int )


@Hr – 1 for Extended Style (Embedded Human Readable Text) and 0 for Standard Style

Fonts

https://barcoderesource.com/upcean_barcodefont.html

EAN13
    
	dbo.EncodeEAN13( @InputData nvarchar(255) , @Hr int )


@Hr – 1 for Extended Style (Embedded Human Readable Text) and 0 for Standard Style

Fonts

https://barcoderesource.com/upcean_barcodefont.html

Standard Style

  • CCodeUPCEAN_S1
  • CCodeUPCEAN_S2
  • CCodeUPCEAN_S3
  • CCodeUPCEAN_S4
  • CCodeUPCEAN_S5
  • CCodeUPCEAN_S6
  • CCodeUPCEAN_S7
Extended Style

  • CCodeUPCEAN_HRBS1
  • CCodeUPCEAN_HRBS2
  • CCodeUPCEAN_HRBS3
  • CCodeUPCEAN_HRBS4
  • CCodeUPCEAN_HRBS5
  • CCodeUPCEAN_HRBS6
  • CCodeUPCEAN_HRBS7
EXT2
    
	dbo.EncodeEXT2( @InputData nvarchar(255) )


Fonts

https://barcoderesource.com/upcean_barcodefont.html

EXT5
    
	dbo.EncodeEXT5( @InputData nvarchar(255) )


Fonts

https://barcoderesource.com/upcean_barcodefont.html

I2of5
    
	dbo.EncodeI2of5( @InputData nvarchar(255), @CheckDigit int )


@CheckDigit - 1 for Check Digit, 0 for no Check Digit

Fonts

https://barcoderesource.com/i2of5_barcodefont.html

ITF14
    
	dbo.EncodeITF14( @InputData nvarchar(255), @CheckDigit int, @ItfRectangle int )


@CheckDigit - 1 for Check Digit, 0 for no Check Digit

@ItfRectangle - 1 for Rectangle Bearers Bar, 0 for Top/Bottom Bearers Bar

Fonts

https://barcoderesource.com/itf14_barcodefont.html

POSTNET (Use Font Size 9)
    
	dbo.EncodePOSTNET( @InputData nvarchar(255) )


Fonts

https://barcoderesource.com/postnet_barcodefont.html

GS1 128 (UCCEAN)
    
	dbo.EncodeUCCEAN( @InputData nvarchar(255))
Fonts

https://barcoderesource.com/code128_barcodefont.html

UPCA
    
	dbo.EncodeUPCA( @InputData nvarchar(255), @Hr int )


@Hr – 1 for Extended Style (Embedded Human Readable Text) and 0 for Standard Style

Fonts

https://barcoderesource.com/upcean_barcodefont.html

Standard Style

  • CCodeUPCEAN_S1
  • CCodeUPCEAN_S2
  • CCodeUPCEAN_S3
  • CCodeUPCEAN_S4
  • CCodeUPCEAN_S5
  • CCodeUPCEAN_S6
  • CCodeUPCEAN_S7
Extended Style

  • CCodeUPCEAN_HRBS1
  • CCodeUPCEAN_HRBS2
  • CCodeUPCEAN_HRBS3
  • CCodeUPCEAN_HRBS4
  • CCodeUPCEAN_HRBS5
  • CCodeUPCEAN_HRBS6
  • CCodeUPCEAN_HRBS7
UPCE
    
	dbo.EncodeUPCE( @InputData nvarchar(255), @Hr int )


@Hr – 1 for Extended Style (Embedded Human Readable Text) and 0 for Standard Style

Fonts

https://barcoderesource.com/upcean_barcodefont.html