Post reply

Query returns incorrect value

  • November 7, 2016 at 8:13 am

    #314765

    Table PEOPLE_EMAIL_JRW contains 1 row:

    IDPOSPERSON_EMAIL_TYPESPERSON_EMAIL_ADDRESSESPERSON_PREFERRED_EMAIL

    22222221PRdmanke@yahoo.comY

    I have a bit of .NET code that executes this statement:

    exec sp_executesql N'SELECT COUNT (*) FROM PEOPLE_EMAIL_JRW WHERE ID = ''@COLLEAGUE_ID''',N'@COLLEAGUE_ID varchar(7)',@COLLEAGUE_ID='2222222'

    and returns a value of 0

    When I manually run a query:

    Select Count(*) from PEOPLE_EMAIL_JRW where ID = '2222222' it returns a value of 1.

    Why do these 2 queries return different values?

  • November 7, 2016 at 8:18 am

    #1910931

    I think it's those double single quotes either side of @COLLEAGUE_ID. Try removing them and see whether you get the correct result.

    John

  • November 7, 2016 at 8:22 am

    #1910935

    It is indeed the quotes causing the problem. As written, the query is looking for an ID equal to the string literal '@COLLEAGUE_ID' because it's in quotes.

    exec sp_executesql N'SELECT COUNT (*) FROM PEOPLE_EMAIL_JRW WHERE ID = @COLLEAGUE_ID',N'@COLLEAGUE_ID varchar(7)',@COLLEAGUE_ID='2222222'

    There's no need for dynamic SQL here though, you could just do

    DECLARE @COLLEAGUE_ID varchar(7) ='2222222';

    SELECT COUNT (*) FROM PEOPLE_EMAIL_JRW WHERE ID = @COLLEAGUE_ID;

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 7, 2016 at 8:45 am

    #1910945

    You are correct. Thank you for the swift response

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply