Re: Different Row Counts From Two Virtually Identical Queries

From: Aaron Bertrand [MVP] (aaron_at_TRASHaspfaq.com)
Date: 03/19/04 

Date: Fri, 19 Mar 2004 11:54:22 -0500

Query 1 is looking for the following value:

'smith'

If you are passing your value directly into a variable, you don't need to
escape/delimit the string. 

-- 
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Bruce Rose" <brose@snl.com> wrote in message
news:u%23zVoDdDEHA.1456@TK2MSFTNGP09.phx.gbl...
> 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
>
>
>
> 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
>
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: This is brilliant! Just look how long it is! Ha ha ha ha ha ha
    ... declare CRS cursor scroll read_only for select recordid from junk ... www.15seconds.com is useful but most of the articles are total tripe ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: OT for the Record herein: Who is Michael NG
    ... Michael's reply to be troll chow (Though ... and I declare JAM's question to be out of order. ... Bruce (Did I just exceed my authority as Head Up? ... I bow to you omnipotent authority. ...
    (rec.collecting.coins)
  • Re: OT for the Record herein: Who is Michael NG
    ... and I declare JAM's question to be out of order. ... Bruce (Did I just exceed my authority as Head Up? ... To email me, remember that RoadRunner ... I bow to you omnipotent authority. ...
    (rec.collecting.coins)
  • Re: Magic is gonna be great
    ... It is more than a little pretentious and condescending to declare that ... "Bruce is back," when he never went anywhere. ... Bruce Springsteen assembled a new band and recorded ... new band on a world tour, which eventually spawned a live CD and DVD ...
    (rec.music.artists.springsteen)
  • Re: a Flash methods question?
    ... What a junk. ... You could have at least considered to declare `movieplayed'. ... One does not declare, within a function, any global variables that it ...
    (comp.lang.javascript)