nvarchar concatenation with 'foo' or N'bar'

Question

nvarchar concatenation with 'foo' or N'bar'

whatispunk

Old Hand

Points: 356
More actions
July 18, 2008 at 11:58 am

#210179

What happens to nvarchar variables when different concatenations are performed. (This is for a dynamic sql call).
Given the following code:
DECLARE @sql nvarchar(MAX)
DECLARE @where nvarchar(MAX)
SET @where = ' WHERE Name LIKE ''%' + @Name + '%'' '
SET @sql = 'SELECT * FROM Customers' + @where
exec sp_executesql @sql
Question 1:
Since @where and @sql are both nvarchar(MAX) what issues might arise with string size and overflowing?
Question 2:
I didn't prefix each string with N to designate it as an nvarchar string. What issues might this cause?
Question 3:
Is this the more correct code? This can get ugly if your @sql has lots of different variables concatenated into it. Is every N prefix necessary?
DECLARE @sql nvarchar(MAX)
DECLARE @where nvarchar(MAX)
SET @where = N' WHERE Name LIKE ''%' + @Name + N'%'' '
SET @sql = N'SELECT * FROM Customers' + @where
exec sp_executesql @sql
Question 4:
If I omit one N prefix what will the result be? For example (notice the missing N at the end):
SET @where = N' WHERE Name LIKE ''%' + @Name + '%'' '
I really appreciate any answers. Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Have you taken the samples you provided, and run them in your database on your dev server?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

whatispunk Old Hand Points: 356 More actions · Answer 2

Yes it does return data but what will happen if a unicode character makes its way into one of the variables?

Imagine we have @var nvarchar(MAX) and it contains unicode data. Will it remain unicode (nvarchar) if its concatenated with a non unicode string.

@var + 'non unicode'

What is the result of the above statement? And what is the difference if we use the following statement instead:

@var + N'non unicode'

Thanks.

GSquared SSC Guru Points: 260824 More actions · Answer 3

Well, I hadn't played with that, so I tested it.

declare @NV nvarchar(max), @v-2 varchar(max)

select @NV = NChar(0641), @v-2 = 'a'

select @NV, @v-2

select @NV = @NV + @v-2

select @NV

The result of the concatenation is ?a, which is exactly what it should be.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

whatispunk Old Hand Points: 356 More actions · Answer 4

Interesting. I've done a bunch more testing here and it seems the only time you need to prefix a literal string with N is when the literal string itself contains unicode characters.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

Please read BoL topics "Implicit Conversions" and "Data Type Precedence" that define the rules, such that " N'a' + 'b' " is internally converted into " N'a' + cast('b' as nchar(1) ) ". That implicit data type conversion occurs is often the cause of performance problems.

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.
When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.

SQL = Scarcely Qualifies as a Language

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 6

whatispunk (7/18/2008)
What happens to nvarchar variables when different concatenations are performed. (This is for a dynamic sql call).
Given the following code:
DECLARE @sql nvarchar(MAX)
DECLARE @where nvarchar(MAX)
SET @where = ' WHERE Name LIKE ''%' + @Name + '%'' '
SET @sql = 'SELECT * FROM Customers' + @where
exec sp_executesql @sql

Hi whatispunk!

For this specific example you would not need dynamic sql, you could use this instead:

DECLARE @param nvarchar(MAX);

SET @param = N'%' + @Name + N'%';

SELECT * FROM dbo.Customers WHERE Name LIKE @param;

Best Regards,

Chris Büttner

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 7

Ya beat me to it, Chris. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 8

Christian Buettner-167247

SSChampion

Points: 13729

July 21, 2008 at 1:50 pm

#845997

Just luck 😉

Best Regards,

Chris Büttner