LearnNowOnline

Blog

Transact-SQL and Data Types

Excerpt by Don Kiely | March 27, 2013

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;

The 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.

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;

GO

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.

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