Sql Server. Why field value of [almost] any type may be treated as quoted string in Query/DML?

Question

I discovered SUDDENLY that in SQL Server (2000) any field type value may be treated as quoted string (in query or DML).

Question: Is it normal behavior or accidentally successful result?

Example: 
CREATE TABLE [Test_table] (
    [int_field] [int] NULL ,
    [float_field] [float] NULL ,
    [date_field] [datetime] NULL ,
    [id] [int] NOT NULL 
) ON [PRIMARY]
GO

update test_table set int_field = 100, float_field = 10.01, date_field = CAST('2013-11-10' AS DATETIME) where id = 1

update test_table set int_field = '200', float_field = '20.02', date_field = '2014-12-10' where id = '2'

select * from test_table where id in ('1', 2) -- WHY '1' DOES WORK!???

Why i need this? It exists idea to send in one Stored Procedure over 270 parameters as integral text (XML or custom serialization by delimiters or like Len1+value1+len2+value2+..) then parse and extract all desired values and use them in UPDATE statement. This SO Question.

Q2: Is there any limitations for some types? Q3: Is this reliable way or CAST anyway is recommended?

Yuu

Answer 1

If you check the CAST and CONVERT topic, you'll find a handy table:

You'll note that conversion from char and varchar is supported for every other type, and only a few of them require explicit casts. For some types, there's no obvious way to type a literal of that type, so allowing implicit conversions from a string makes sense.

(But oh, how I wish conversion to datetime required an explicit case with a format code...)

Berretta

Answer 2

SQL Server, like most (all?) brands of SQL, automatically attempts to cast things to the correct type. This is pretty standard behavior.

It should be reliable in the above cases. In both the update and select statement, the type that must be converted to is known (from the column definitions of the tables).

However, automatic casting can introduce subtle issues when it is part of a more complex query. Some kinds of SQL will have problems with statements like this:

select case when foo=1 then 0 else 'a' end from table

In this case, the result type won't necessarily be something that can accept all types of results, so it could fail when it tries to assign 'a'. Be careful when relying on automatic conversion in complex statements. It is probably better to make it explicit in such cases.

Another potential issue with passing everything in as strings is that there will be an error if you accidentally pass in a non-numeric value.

Barricade

Your CASE example is wrong. The type of the result of a CASE expression is always fixed, and follows precedence rules between all possible types in THEN (and ELSE) clauses. The result of the expression you've shown can only ever be of type int. The interpreter doesn't "guess". So, it can only ever (in this case) produce 0 or a conversion error – Berretta

@Damien_The_Unbeliever, thanks for the additional information. I have changed it to remove assumptions about what the interpreter is actually doing. – Barricade