Select does not work with variable in SQL Server 2017

Question

I have a table called UserList which has UserName and UserPassword columns. When I query as below, it Works and returns the right data.

SELECT * FROM dbo.UserList WHERE UserName = 'aliserin'

But when I try it as below, it doesn't give me any result. It just returns empty. It doesn't give an error, but I really didn't understand the reason. It is a local database if it matters.

declare
@UserName as NVARCHAR

SET
@UserName = 'aliserin'
SELECT * FROM dbo.UserList WHERE UserName = @UserName

When I try the same code on UserId constraint as below, it works. Does anyone has any idea why?

DECLARE @UserName1 AS NVARCHAR

SET @UserName1 = 2

SELECT * FROM dbo.UserList WHERE UserId = @UserName1

Dai · Accepted Answer · 2017-09-03 00:19:31Z

6

You need to specify a length parameter for the varchar type: What is the effect of omitting size in nvarchar declaration

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

This means your query is actually doing SELECT * FROM UserList WHERE UserName = 'a'.

So you want:

DECLARE @userName AS nvarchar(8)
SET @userName = 'aliserin'
SELECT * FROM dbo.UserList WHERE UserName = @userName

You can also simplify this by using the DECLARE = syntax and omitting the AS keyword (I avoid using AS for types as it's usually used to alias column names)

DECLARE @userName nvarchar(8) = 'aliserin'
SELECT * FROM dbo.UserList WHERE UserName = @userName

answered Sep 3, 2017 at 0:19

Dai

158k31 gold badges314 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select does not work with variable in SQL Server 2017

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related