Ridiculous thing in SQL Select query

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 = 'abc@yahoo.com'

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

1111    abc    abc@yahoo.com

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, Jul 23 '13 at 06:22
You might want to look at this [answer](http://stackoverflow.com/a/4628331/15498) for links to connect items asking Microsoft to make (opt-in) stricter settings available. — Damien_The_Unbeliever, Jul 23 '13 at 06:23
The ID is wrong but so is the stored procedure so together they are right! — Toby Allen, Jul 23 '13 at 06:25
possible duplicate of [SQL Server silently truncates varchar's in stored procedures](http://stackoverflow.com/questions/4628140/sql-server-silently-truncates-varchars-in-stored-procedures) — Mat, Jul 23 '13 at 06: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, Jul 23 '13 at 06:33

score 5 · Accepted Answer · answered Jul 23 '13 at 06:22

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 '13 at 06:22

andrewb

2,995
7
54
95

could you tell me How I can fix it? OR which type I should use for LoginID? – Tung Pham Jul 23 '13 at 06:24
1

you can set `@LoginID` more than 4 then it will not show the result – Dinup Kandel Jul 23 '13 at 06:25
1

Simply do not set the nvarchar to 4 (or any value) or set it to a value higher than the length of your input. – andrewb Jul 23 '13 at 06:26
1

@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. – ErikE Jul 23 '13 at 06:32
@ErikE - `varchar(max)` must surely come close to a lengthless parameter? – Damien_The_Unbeliever Jul 23 '13 at 06:34
@Damien_The_Unbeliever How close is 2147483647 to infinity? In my book `1/infinity = 2147483647/infinity`. `varchar(max)` has a length. – ErikE Jul 23 '13 at 13:27
@ErikE - ah, but [what is the limit](http://stackoverflow.com/questions/7611394/maximum-size-of-a-varcharmax-variable)? I believe that a parameter would follow the same path as a variable, rather than a non-FILESTREAM column. – Damien_The_Unbeliever Jul 23 '13 at 13:56
@Damien_The_Unbeliever Interesting, but it doesn't really change my point. `varchar(max)` is not a good data type to use unless you expect your data to exceed 8000 characters. – ErikE Jul 23 '13 at 15:19
@ErikE - well, I guess I've missed what you're aiming for. Your first comment stated that there's no "lengthless" option available (whatever that may mean). I've then pointed you at options that have no (apparent) length limit. Whilst I agree with your general gist (specify the length if you can), I'm not sure what point you're trying to make more broadly. Can you point to a language that has a lengthless string that is worth studying? – Damien_The_Unbeliever Jul 23 '13 at 17:53
@Damien_The_Unbeliever Sure, I'll be more clear. `varchar(max)` is not lengthless. It has a length of `max`, which is whatever the maximum limit is in that version of SQL Server. Furthermore, if the variable is to be used to reference a column's value in a table, then it IS limited to some fixed amount, as shown in the same post that you linked me to above. – ErikE Jul 23 '13 at 17:57

score 2 · Answer 2 · answered Jul 23 '13 at 06:26

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

Ridiculous thing in SQL Select query

2 Answers2