Friday, April 1, 2011

Nvarchar VS Nvarchar(n) in T-SQL

Recently i encountered a problem which was weired in nature...but yes not very obvious one..took some time to figure out posting here for benefit of other netizens...

Can you spot out the difference between two statments below if yes you are way smarter than me and please log off.....else please continue reading :)

STATEMENT 1:-
***********************************************************************
select EmployeeLookupID from PerformanceReview..EmployeeLookup ELookup
where(
(ELookup.ManagerEmployeeId=N'manager')
OR (ELookup.OneUpManagerEmployeeId=N'manager')
OR (ELookup.EmployeeLookupId=4)
OR (ELookup.ZoneLookupId in
(Select Zlookup.ZoneLookupId from PerformanceReview..ZoneLookup
AS Zlookup where Zlookup.ZoneHRManagerEmployeeId= N'manager')
)
)
Ouput based on my DB records was TWO only and this is what i expected
4
6


************************************************************************

STATEMENT 2:-
*************************************************************************
declare @EmployeeLookupID int
declare @EmployeeID nvarchar
set @EmployeeLookupID =4
set @EmployeeID =N'manager'
select EmployeeLookupID from PerformanceReview..EmployeeLookup ELookup
where(
(ELookup.ManagerEmployeeId=@EmployeeID)
OR (ELookup.OneUpManagerEmployeeId=@EmployeeID)
OR (ELookup.EmployeeLookupId=@EmployeeLookupID)
OR (ELookup.ZoneLookupId in
(Select Zlookup.ZoneLookupId from PerformanceReview..ZoneLookup
AS Zlookup where Zlookup.ZoneHRManagerEmployeeId= @EmployeeID)
)
)
Ouput based on my DB records was one only and this is what i NOT EXPECTED :(
4

*************************************************************************

the difference between the mismatch of expecattions is
if you have noticed the variable to which i am assinging value is nvarchar & this column(ManagerEmployeeId & OneUpManagerEmployeeId & ZoneHRManagerEmployeeId) is nvarchar(100) and there was some implicit casting going on......behind the scenes :(
so I modified my statmenet 2 to below
MODIFIED STATEMENT 2:-
*************************************************************************
declare @EmployeeLookupID int
declare @EmployeeID nvarchar(100)
set @EmployeeLookupID =4
set @EmployeeID =N'manager'
select EmployeeLookupID from PerformanceReview..EmployeeLookup ELookup
where(
(ELookup.ManagerEmployeeId=@EmployeeID)
OR (ELookup.OneUpManagerEmployeeId=@EmployeeID)
OR (ELookup.EmployeeLookupId=@EmployeeLookupID)
OR (ELookup.ZoneLookupId in
(Select Zlookup.ZoneLookupId from PerformanceReview..ZoneLookup
AS Zlookup where Zlookup.ZoneHRManagerEmployeeId= @EmployeeID)
)
)
Ouput based on my DB records was one only and this is what i NOT EXPECTED :(
4

*************************************************************************
and it returned me two rows as i expected...always look up to what is the column datatype and use the variable in your stored procedure also of that data type is what i learnt ......

still trying to figure out why Nvarchar is different than nvarchar(100) in above case...

Take care till than bbye

No comments:

Post a Comment