Query analyzer vs Stored procedure shows different results

  • Okay, I'm losing my mind on this one. Please help.

    In Query analyzer, my select statement works fine, but when I put it into a stored procedure, the SQL server does not process my where clause.

    I even tried it in a View and it works correctly there, but in the stored procedure, it will NOT work.

    Here is the Select command.

    SELECT

    DEPTID_DEPTCODE_XREF_ID,

    x.DEPTID,

    DEPARTMENT_NAME,

    p.descr,

    DEPT_CODE,

    ACTIVE

    FROM Intranet.dbo.DEPTID_DEPTCODE_XREF X

    left join Intranet.dbo.vw_Adsmgt_PS_RemoteLocations P

    on substring(x.deptid,1,3) = substring(p.deptid, 1,3)

    where substring(p.deptid,1,3) NOT in ('021','037')

  • Actually it should always fail... The last where condition will always fail is the left join returns nothing on the outer table. (substring(null) = null).

    Put that where condition in the joi part and you should see some improvements.

  • I changed the statement to this:

    SELECT

    DEPTID_DEPTCODE_XREF_ID,

    x.DEPTID x,

    p.deptid p,

    DEPARTMENT_NAME,

    p.descr,

    p.deptid as pdeptid,

    DEPT_CODE,

    ACTIVE

    FROM Intranet.dbo.DEPTID_DEPTCODE_XREF X

    left join Intranet.dbo.vw_Adsmgt_PS_RemoteLocations P

     on substring(x.deptid,1,3) = substring(p.deptid, 1,3)

    and substring(x.deptid,1,3) NOT in ('021','037')

     

    In query analyzer, the results don't come back right. It's not processing the filtering after the "and" Statement. Inotherwords, it's doing the same thing as the stored procedure was doing with the other query.

    With QA, using the where statement, I get 19 rows back.

    Changing it the the above code, I get 51 rows back. It's not processing the "not in ('021','037').

    I'm still confused.

  • Follow this.. can't help you without ALL that info :

    Help us help you

  • Very very bizzarre thing today. I had a coworker look over it, and he forced the stored proc to recompile from query analyzer (I was using EM to make changes) and suddenly it began acting like it is supposed to. Just one of those head scratchers, but it's working now.

  • Doh!! Forgot the easy fix : DBCC FREEPROCCACHE

    the server was using a cached copy on some of those calls.

  • The thought did cross my mind that the stored proc was being cached, but I was not aware of how to make it flush the cache. Thanks for the tip.

  • Let's put it that way... it's always cached untill it is dropped from the cache by the server, or when you recompile the sp (or change the definition). Also you may have 4-5 concurrent plans at the same time for the same proc depending on how you call em :

    Exec dbo.MyProc

    Exec MyProc

    exec dbo.myproc...

Viewing 8 posts - 1 through 7 (of 7 total)

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