SQL Server | String Comparison

Question

I am facing some strange issue and want to understand the reason behind this.

We have two database servers Say A and B. On both of these servers we have our application database (Same schema but different records)

Problem : We have a SqlQuery

Select * from Person where memberId=123456

This query runs perfectly and return the rows selected on server - A. But the same query on a different server-B doesnt return any records.

But if i modify my query to

Select * from Person where memberId='123456'

( notice the single quotes)

Now it returns me proper records.

DataType of memberId is nchar(100) . Technically i understand that i should compare it using the single quotes.

But just want to understand why is this happening??

Update : 1) Both have exactly the same schema. 2) Both have same records

Actual Code :

Actually this query is a dynamically created and then executed using

declare @sql varchar(2000)
    set @sql = 'SELECT * FROM PersonTrivia where memberId='+ @MemberId
    print @sql 
    exec (@sql)

and this parameter @MemberId is varchar(250)

Sorry to ask what might seem like an obvious question; you've double checked that the schema for both the tables is the same? — GrandMasterFlush
– GrandMasterFlush, Commented Sep 9, 2011 at 8:46
Interesting question but why on earth are you using a fixed width 200 byte column nchar(100) to store an int? — Martin Smith
– Martin Smith, Commented Sep 9, 2011 at 8:49
Which SQL Server versions are these (which is which)? Also, is the collation order for the field memberId the same? — Moritz Both
– Moritz Both, Commented Sep 9, 2011 at 8:49
Is there a nchar type in SQL Server 2005? In from MSDN, looks like it is not msdn.microsoft.com/tr-tr/library/ms182673%28v=sql.100%29.aspx — Soner Gönül
– Soner Gönül, Commented Sep 9, 2011 at 8:58
@Soner - Yes there definitely is. According to the data type precedence rules when comparing an nchar column to an int literal the column should get implicitly cast to an int, can't think of a way that these results can happen. — Martin Smith
– Martin Smith, Commented Sep 9, 2011 at 9:01

Carlos Quintanilla · Accepted Answer · 2011-09-09 09:16:50Z

2

Does the query return no records or it gives you an error?

It looks like you can enter numbers into a nchar field, however, the first time you add a character you won't be able to query for "integers" anymore... or at least it seems so.

CREATE TABLE [dbo].[testnchar](
    [id] [nchar](10) NULL,
    [name] [nchar](100) NULL
)
GO

insert testnchar
select 1, 222222

select * from testnchar 
where name = 222222

id         name
---------  --------
1          222222 

insert testnchar
select 1, 'test'

select * from testnchar 
where name = 222222

--Msg 245, Level 16, State 1, Line 1
--Conversion failed when converting the nvarchar value 'test       

delete testnchar 
where name = 'test'

select * from testnchar 
where name = 222222

id         name
---------  --------
1          222222

answered Sep 9, 2011 at 9:16

Carlos Quintanilla

13.4k3 gold badges24 silver badges25 bronze badges

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

1 Comment

Martin Smith Over a year ago

The definite implication from the OP is that they just get no results rather than an error though. That would be quite a crucial bit of information to leave out!

Paul Walls · Accepted Answer · 2011-09-10 11:42:00Z

2

I will be very interested to know if you come up with another reason for why this is occurring, but I am starting to doubt it. I am fairly certain you are catching or suppressing an error somewhere.

Consider the following:

CREATE TABLE [Person]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [MemberID] [nchar](200) NULL,
    [Data] [varchar](50) NULL,
    CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )
)

INSERT Person([MemberID],[Data]) VALUES ('1111111111', 'Test1');
INSERT Person([MemberID],[Data]) VALUES ('2222222222', 'Test2');
INSERT Person([MemberID],[Data]) VALUES ('3333333333', 'Test3');
INSERT Person([MemberID],[Data]) VALUES ('NON-NUMERIC', 'Test4');

SELECT * FROM Person WHERE MemberID = 2222222222

The query above will return (1) result AND an error. So, if your code looked like the following:

command = new SqlCommand( 
     @"SELECT * FROM Person WHERE MemberID = 2222222222", connection );
try
{
    reader = command.ExecuteReader();
    while ( reader.Read() )
    {
        Console.WriteLine( "MemberID = " + reader["MemberID"] );
    }
    // We'll never get here.
    reader.Close();
}
catch { }

The result you would get would be MemberID = 2222222222. This could also occur if you were using SQL TRY...CATCH blocks. However, if we change the order¹ of the records:

TRUNCATE TABLE [Person]

INSERT Person([MemberID],[Data]) VALUES ('NON-NUMERIC', 'Test1');
INSERT Person([MemberID],[Data]) VALUES ('1111111111', 'Test2');
INSERT Person([MemberID],[Data]) VALUES ('2222222222', 'Test3');
INSERT Person([MemberID],[Data]) VALUES ('3333333333', 'Test4');

You will essentially get (0) results before the exception is thrown. Finally, if you changed your query to:

SELECT T.* FROM
(
    SELECT TOP 100 *
    FROM Person
    ORDER BY MemberID
) T
WHERE T.MemberID = 2222222222

... you'd get (1) record and an error.

My advice would be to find out if and why you are suppressing an error. My overall advice is to not compare a character field to an integer and rely on an implicit conversion.

^{1. Clustered indexes don't guarantee row order. It almost certainly will in this test, but it was worth pointing out.}

answered Sep 10, 2011 at 11:42

Paul Walls

6,0942 gold badges24 silver badges23 bronze badges

2 Comments

Martin Smith Over a year ago

I can't see any way that the literal 123456 could ever cause the described behaviour. Hopefully if the OP reveals the value they are actually using things will become clearer.

Paul Walls Over a year ago

My guess is that the implicit conversion to int caused by 123456, or whatever value, is causing an error. The OP said the SQL is in a stored procedure, and I'm guessing it is being called from code that is handling errors. When you had him do the CAST(memberId AS INT) test, he probably did it from a query window, and not within the problem stored procedure.

pabdulin · Accepted Answer · 2011-09-09 09:23:44Z

0

Obviously memberId is converted to int before comparison (I assume there is no errors in query exec on 2nd machine?). So my first guess would be that this is machine of Sql Server culture specific, i.e. memberId can be converted to int on first machine and can't on other. Or, since records differ (?) there is just some "wrong" records on 2nd machine. However last thing should result in runtime error.

edited Sep 9, 2011 at 9:23

answered Sep 9, 2011 at 9:17

pabdulin

35.6k9 gold badges66 silver badges102 bronze badges

1 Comment

Gaurav Over a year ago

Petr- Not sure about SQL server culture Specific thing , cause both these servers are maintained by our hosting provider

Collectives™ on Stack Overflow

SQL Server | String Comparison

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related