September 2, 2005 at 8:54 am
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')
September 2, 2005 at 9:05 am
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.
September 2, 2005 at 10:40 am
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.
September 2, 2005 at 11:24 am
Follow this.. can't help you without ALL that info :
September 2, 2005 at 2:01 pm
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.
September 2, 2005 at 2:06 pm
Doh!! Forgot the easy fix : DBCC FREEPROCCACHE
the server was using a cached copy on some of those calls.
September 2, 2005 at 2:12 pm
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.
September 2, 2005 at 2:15 pm
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