How INT database field gets compared to VARCHAR ty

问题:

I have the following stored procedure, not writing the complete stored procedure but some of it is:

@course int = null,
    SET @query = @query + 'Where course_id='+ cast(@course as varchar)

I want to know when I have converted the @course as the VARCHAR type while my course_id in the database is of INT type - how the comparison takes place?

回答1:

The comparison between two different types in SQL Server takes place like this:

• First the two type's precedence is compared based on the rules of Data Type Precedence:

  the data type with the lower precedence is converted to the data type with the higher precedence

• next the type with lower precedence is casted to the type with higher precedence

• the comparison is then done between the original value of the type with higher precedence and the converted value of the type with lower precedence

For the sake of discussion: an INT (precedence 16, high) and a VARCHAR (precedence 27, low) would compare by casting the VARCHAR to INT and then comparing the two.

In your case though there is no conversion occuring, because what happens is that the @course value gets appended to the dynamically constructed @sql. Needless to say, that is bad. the proper solution is to pass down the @course as a parameter to the dymnamic SQL invocation:

@course INT = null
...
 SET @query = @query + 'Where course_id= @course';
...
exec sp_executesql @sql, '@course int', @course;

This is:

• faster: parameterized query instead of hard codded value
• safer: lower the risk of SQL injection if code gets refactored and @counter becomes a type tha can carry SQL injection
• less error-prone: no risk of NULL value propagating to nullify the entire @sql

回答2:

The rationale to the code you posted is that the data type change needs to occur for SQL Server to allow the string concatenation to happen. The statement itself is a string, submitted to the optimizer, which sees the comparison as the correct data types - INT to INT.

You can test & confirm for yourself using the following in Management Studio/Toad/etc:

DECLARE @course INT
    SET @course = 1234

SELECT 'Where course_id='+ @course

This will fail, with an error reading:

Conversion failed when converting the varchar value 'Where course_id=' to data type int.

...while this:

DECLARE @course INT
    SET @course = 1234

SELECT 'Where course_id='+ CAST(@course AS VARCHAR) AS output

...will return:

output
---------------------
Where course_id=1234

There are other means of approaching dynamic SQL. I highly recommend reading this article -- The Curse and Blessings of Dynamic SQL -- on the subject.

回答3:

You seem to be confusing comparison (a = b) and assignment (SET @a = b)

In a comparison between VARCHAR and INT, the former is always being cast to the latter, so this query will fail:

SELECT  1
WHERE   'ab' = 1

, since 'ab' is not castable to INT.

In an assignment, the source value (b) is always cast to the type of the target (@a):

DECLARE @av VARCHAR(100)
SET     @av = 1
GO

DECLARE @ai INT
SET     @ai = 'ab'
GO

Since an INT is always castable to a VARCHAR, the first batch will always succeed, unlike the second batch which casts a VARCHAR to an INT.

回答4:

In your query, you're actually just building another (dynamic) query. So, if you had for example:

SET @course = 2
SET @query = 'SELECT * FROM Courses'
SET @query = @query + 'Where course_id='+ cast(@course as varchar)

the value of @course is converted to characters, appended to the @query and in the end it's:

SELECT * FROM Courses Where course_id=2

which is perfectly valid sql without the need to convert anything before executing.