2

In SQL Server (2018 I think? I don't know how to tell) my variable isn't working in WHERE clauses for NVARCHAR. The comparison should return values but it returns nothing. If I just type the declared text in manually it suddenly works and returns values. There's no logical reason this should be any different, the types are both NVARCHARS. It is working for dates and numbers for me.

The following SQL Server code works properly and returns results:

SELECT * FROM table WHERE Column = 'text'

The following code fails however by coming up empty when I use an initial declare statement:

DECLARE @Class AS NVARCHAR = 'text'

SELECT * FROM table WHERE Column = @Class

I can't get the variable to work in the WHERE clause even though I have confirmed that column is an NVARCHAR. Other parameters I'm declaring work just fine it just seems to be NVARCHAR giving me issues.

Is there something I'm missing?

Dale K
  • 25,246
  • 15
  • 42
  • 71
SandGrug
  • 25
  • 4

2 Answers2

3

This does not do what you think it does:

DECLARE @Class AS NVARCHAR = 'text';
  • You must specify the length parameter n with varchar(n), nvarchar(n), and varbinary(n) column types, because if you don't it's equivalent to n = 1.
  • So DECLARE @Class AS NVARCHAR = 'text' is the same as:
    • DECLARE @Class AS nvarchar(1) = 'text'
    • Which means @class = N't' and not N'text' (but it would be if you put DECLARE @Class AS nvarchar(4).

You can see it by running this:

DECLARE @Class AS NVARCHAR = 'text'
SELECT @class;

...which gives you t as output:

enter image description here

Therefore, change your SQL to this:

DECLARE @class nvarchar(4) = N'text';

SELECT * FROM table WHERE Column = @class;

Also, don't forget to always schema-qualify your object-names: i.e. prefer dbo.table to just table.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Dai
  • 141,631
  • 28
  • 261
  • 374
0

You need to declare the length of nvarchar. For example:

DECLARE @Class AS NVARCHAR(10) = 'text'

You can check the value of @Class by running 'select @Class' to see what it contains.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Gary
  • 9
  • 2
  • 2
    Please clarify how this is an improvement over the existing answer which explains this and more? – Dale K Nov 18 '22 at 03:03
  • Hi Dale at the time i started the post your answer wasn't there :) Your response is more thorough i would agree. Have a little patience with newbies :) Seems a little tense here. – Gary Nov 18 '22 at 03:08
  • Don't forget to prefix your `NVARCHAR` literal value with `N` (e.g. `N'text'`). – J.D. Nov 18 '22 at 04:00
  • Appreciate the shorter explanation fix too thanks – SandGrug Nov 18 '22 at 04:15
  • Thanks:). My first post in stack overflow :). – Gary Nov 18 '22 at 04:22