Ridiculous thing in SQL Select query [closed]

Question

I have a query like this:

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[User_SelectByLoginID] @LoginID nvarChar(4) as SELECT dbo.[User].* FROM dbo.[User] WHERE LoginID=@LoginID

And data in the User table:

LoginID ='1111' | Name ='abc' | Email = '[email protected]'

when I executed this query and typed in '1111111', it returned the record:

1111 abc [email protected]

it is ridiculous when I entered the wrong LoginID and still got the data.

P/S: I set LoginID nvarchar(4)

Can someone explain for me? And how to make it right?

Are you just wanting to rant/vent about the fact that SQL Server silently truncates overlong parameters? Or do you have an actual question? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 23, 2013 at 6:22
You might want to look at this answer for links to connect items asking Microsoft to make (opt-in) stricter settings available. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 23, 2013 at 6:23
The ID is wrong but so is the stored procedure so together they are right! — Toby Allen
– Toby Allen, Commented Jul 23, 2013 at 6:25
possible duplicate of SQL Server silently truncates varchar's in stored procedures — Mat
– Mat, Commented Jul 23, 2013 at 6:27
@Damien_The_Unbeliever this is my first time coming cross this thing, so I just want to ask to make it clear. Don't think negatively like that. Thank you! — Tung Pham
– Tung Pham, Commented Jul 23, 2013 at 6:33

andrewb · Accepted Answer · 2013-07-23 06:22:33Z

5

If you set @LoginID to nvarchar(4) it will truncate to that size so really you are passing in 1111 and not 11111111.

answered Jul 23, 2013 at 6:22

andrewb

3,1058 gold badges59 silver badges103 bronze badges

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

10 Comments

Tung Pham Over a year ago

could you tell me How I can fix it? OR which type I should use for LoginID?

Dinup Kandel Over a year ago

you can set @LoginID more than 4 then it will not show the result

andrewb Over a year ago

Simply do not set the nvarchar to 4 (or any value) or set it to a value higher than the length of your input.

ErikE Over a year ago

@andrew-buchan Are you saying to use nvarchar with no parentheses at all? That is a huge mistake, because in various contexts it either means nvarchar(30) or nvarchar(1). Always, always, always specify a data length. In SQL Server there is no concept of a lengthless char/varchar parameter.

Damien_The_Unbeliever Over a year ago

@ErikE - varchar(max) must surely come close to a lengthless parameter?

|

gregjer · Accepted Answer · 2013-07-23 06:26:36Z

SQL Server silently truncates your value passed to stored procedure, so even though you pass value '1111111', it is cut off to the declared length (4) so in your stored procedure there is a value '1111'.

So you should declare your parameter @LoginID to the same size which has your column LoginID in User table

Collectives™ on Stack Overflow

Ridiculous thing in SQL Select query [closed]

2 Answers 2

10 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Linked

Related