CODEWITHSUNDEEP
sql-servert-sqlcharacter-encoding
Marcus L
Marcus L

Reputation: 4078

Why does SQL Server require INT to be converted to NVARCHAR?

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).

Upvotes: 1

Views: 4583

Answers (2)

Svetlozar Angelov
Svetlozar Angelov

Reputation: 21680

+ (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

Upvotes: 2

David Božjak
David Božjak

Reputation: 17645

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)

Upvotes: 1

Related Questions