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?

CodePudding user response：

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:

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.

CodePudding user response：

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.