Microsoft T-SQL: Can I CONVERT smallint to varchar

Question

It may just be me, but... Despite the fact that most sql developers may consider cast & convert to be very basic stuff, and that may be true, I find Microsoft's documentation page on CAST & CONVERT to be one of the most hideous, not-intuitively-laid-out, hard to understand things I have ever seen. Much of their documentation is great. Like constantly trying to blend the entire page into a mix of both cast and convert, jumping back and forth in each sentence... rather than dealing with them separately. And who puts the target_type as the first argument? Putting the expression as the first argument would be more intuitive - and follow the other 99% of numerous programming languages' syntax. UH

MS says that I can only convert to 3 data types: (well actually I'm not really sure if this applies to both CAST and CONVERT, since they ARE, in fact, different... But according to the layout of that webpage, it apparently applies equally to both - even though I already know for a fact that it is not true for CAST, which I use much more frequently).

It says: "Is the target data type. This includes xml, bigint, and sql_variant"

Putting aside for the moment the fact that I CAST things as many other datatypes all the time (date, varchar),

My immediate question is: if I can only CONVERT to those data types, then why does this work?

select CONVERT(varchar(200), cast(50 as smallint))

And finally, I'd like to run an INSERT that will be getting a smallint and putting it into a varchar(200) column.

All I'm trying to do is avoid any failures, so maybe I don't really "need" to convert or cast over to varchar, but any commments on

1. answer on what is my apparent misunderstanding about the CONVERT documentation

or

2. how to safely convert it to insert to varchar

are welcome. As long as you're not just overly unpleasant, since there are always those MS fans who get hot under the collar at all critiques of MS .. :|

Answer 1

Yes, you can convert from smallint to varchar.

1) answer on what is my apparent misunderstanding about the CONVERT documentation

This may be product of general lack of understanding on what data types are, how can they be converted from one type to another and equally important; what styles are when it comes to the aesthetic representation of a data type.

CAST is an explicit cast operation with no style options. CONVERT is also an explicit cast that gives you the ability to specify a style for the output.

The documentation clearly states:

Implicit Conversions

Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST or CONVERT function to be specified. The following illustration shows all explicit and implicit data type conversions that are allowed for SQL Server system-supplied data types. These include xml, bigint, and sql_variant. There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.

For your second question

2) how to safely convert it to insert to varchar

Depending of what you mean by safe. Converting to varchar is the convertion that most likely succeed. But whenever to cast to any toher datatype you are intrinsically changing the very nature of the data and will lose precision when casting to smaller types (or applying styles).

The documentation clearly states:

Truncating and Rounding Results

When you convert character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.

in other words, casting is never safe.

Answer 2

Numbers always get silently truncated for me. I would propose:

Option 1

Compare the converted value with the original value.

DECLARE @ORIGINAL DECIMAL(13,2) = -99999999999.99 -- 
DECLARE @EXPECTED VARCHAR(15) = ''
SELECT @EXPECTED = CONVERT(VARCHAR(15),@ORIGINAL)
IF CONVERT(DECIMAL(13,2),@EXPECTED) != @ORIGINAL SELECT 'Ooops'

Option 2

Make sure that all possible values will fit in target varchar.

Decimal(13,2). Widest number possible will be "-99999999999.99" needs varchar(15):

• 13 chars for digits
• 1 char for decimal separator
• 1 char for minus sign

Smallint stores 2 bytes, from "-32768" to "32767", needs varchar(6): - 5 chars for digits - 1 char for minus sign

Not sure if you need chars for thousands separators, or if you can change it via settings.