Transact-SQL and Data Types

Excerpt by Don Kiely

Some programming languages are quite forgiving about implicitly converting data types in expressions and computations. However, like other languages with strict typing, T-SQL requires explicit conversion between incompatible data types.

For example, if you execute the following script, it will raise an error because T-SQL can't implicitly convert a character string varchar to an int, as shown in the Figure below.

DECLARE @msg varchar(20);
SELECT @msg = 'The result is: ' + (2+2);
PRINT @msg;

T-SQL errorThe moral of the story is: Always perform explicit conversions when you work with different data types. You do that in T-SQL with CAST and CONVERT.


T-SQL supports two functions for data type conversion, CAST and CONVERT. CAST conforms to the ANSI standard, but CONVERT offers extra functionality. Here's the syntax for each:

CAST (expression AS data_type [(length)])
CONVERT (data_type [(length)], expression [, style])

The following examples use CAST and CONVERT to perform explicit data type conversion to make the previous code sample work. Here it is explicitly casting or converting the value 2+2 to a string. The PRINT statement displays the output in the results pane, as shown in the Figure below.

DECLARE @msg varchar(20);
SELECT @msg = 'The result is: ' +
CAST((2+2) AS varchar(1));
PRINT @msg;
SELECT @msg = 'The result is: ' +
CONVERT(varchar(1), (2+2));
PRINT @msg;

result of casting and converting

NOTE: The main use for the PRINT statement is troubleshooting Transact-SQL code. You can also use it for sending informational error messages to client applications, but RAISERROR is preferable because it allows you to return errors with a greater severity level and also gives you more control over the error message. If you want the result returned to the calling code, use SELECT or the return value of a stored procedure instead.

Thumbnail for 566This post is an excerpt from the online courseware for our SQL Server 2012: Introduction to T-SQL course written by expert Don Kiely.

Don Kiely
Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.

This course excerpt was originally posted March 27, 2013 from the online courseware SQL Server 2012, Part 7 of 9: Introduction to T-SQL by Don Kiely

SQL Server