Return Counts Different In Similar SQL Statements

From: Bruce (brose_at_snl.com)
Date: 03/19/04 

Date: Fri, 19 Mar 2004 08:11:07 -0800

Hi all,

   Well I am baffled. Can anyone tell me why these two virtually same queries return different counts? Query 1 returns a count = 0 while query 2 returns a count = 1. I have listed some very simple code you can try in order to see what I am talking about.

create table ##junk ( LastName nvarchar(100) )
go
insert into ##junk values ('smith')
go

declare @s varchar(4000)
declare @sLastName nvarchar(100)
select @sLastName = '''smith'''

select count1 = count(*) FROM ##junk
WHERE (LastName = @sLastName )

select @s =
'select count2 = count(*) FROM ##junk ' +
'WHERE (LastName = ' + @sLastName + ') '
exec (@s)

drop table ##junk
go

    Any insight into this anomaly is appreciated.

Thanks,
Bruce

Relevant Pages