Transact-SQL and Data Types

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.

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.

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:

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.

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 is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Be Sociable, Share!

Leave a Reply

Your email address will not be published. Required fields are marked *