March 19, 2004 at 9:42 am
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
March 19, 2004 at 10:15 am
Hello,
The reason for the difference is that first one is looking for -- 'smith' -- and the second one is looking for -- smith -- . The value that you put into the table is -- smith -- and the value that is in the variable is -- 'smith' --.
The first query uses the variable exactly as it is -- 'smith' --
When you build the second query, the ' marks get incorporated into the whole query string.
I am not sure if I am explaining this very well, so I add 3 lines to your code to help you see what is happening.
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 @sLastName
select * from ##junk;
select count1 = count(*) FROM ##junk
WHERE (LastName = @sLastName )
select @S =
'select count2 = count(*) FROM ##junk ' +
'WHERE (LastName = ' + @sLastName + ') '
select @S
exec (@s)
drop table ##junk
go
I hope this explains it.
Chuck
March 19, 2004 at 11:20 am
Thanks Chuck,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply