views:

611

answers:

2

During an ordeal yesterday, I learned that you can't pass this query to EXEC():

@SQL = @SQL + 'WHERE ID = ' + @SomeID
EXCEC(@SQL)

Where @SomeID is an INT and @SQL is NVARCHAR. This will complain about not being able to convert NVARCHAR back to INT during execution.

I realized you have to do it like

@SQL = @SQL + 'WHERE ID = ' + CONVERT(NVARCHAR(20), @SomeID)

What I didn't understand is why? Why doesn't SQL Server understand the INT when simply +:ed on to an NVARCHAR? I'm guessing it has something to do with char sets.

EDIT: Fixed typos (missed some +:s).

+1  A: 

I'm not saying this will definitely work, and I'm not near my sql management studio to try it before posting, but have you tried something like this:

@SQL = @SQL + 'Where ID = ' + @SomeID
EXEC(@SQL)
Rekreativc
Ach! That's a typo. My bad. + one for spotting, and correcting without mocking me. ;)
Marcus L
+2  A: 

+ (String Concatenation)

An operator in a string expression that concatenates two or more character or binary strings, columns, or a combination of strings and column names into one expression (a string operator).

Expression is any valid Microsoft® SQL Server™ expression of any of the data types in the character and binary data type category, except the image, ntext, or text data types. Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression.

So... There is no implicit convertion of int to string... This is an internal question

Svetlozar Angelov
The next question would of course be why there is no implicit convertion of int to string, but I guess I'll just have to live with it.
Marcus L
I guess it is not allowed because it is too prone to error(there is a chance to loose data (if the nvarchar field is too short ot something...), the same way you can't cast int to bool in C#
Svetlozar Angelov
But implicit conversion of an INT to NVARCHAR is allowed as is implicit conversion of NVARCHAR to INT. See chart on: http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx. However, when seeing a + operator with a number on one and a char/nchar/varchar/nvarchar on the other side, SQL Server attempts the implicit conversion to the numeric type and then doing an add instead of converting the numeric to a string and concatinating. It is ambigous what is wanted. MS (and probably Sybase before them) chose one way. If you want the other, you have to use explicit conversion.
Shannon Severance
To see implicit conversion of an INT to NVARCHAR, create a stored proc that takes an NVARCHAR and then call with an int. It will work, without explicit casting.
Shannon Severance