So I'm updating some legacy code and I get the following error:
The column prefix 'p' does not match with a table name or alias name used in the query.
Hmm... that's weird. I look at the SQL in the query and it looks like this:
...
select
p.*
, t.Name as PersonTypeName
from [Person] p (nolock),
PersonType t (nolock)
inner join #GroupList gl on gl.PersonID = p.PersonID -- this line just added
where p.PersonTypeID = t.PersonTypeID and
p.PersonTypeID = @PersonTypeID and
p.DelFlag = 'N' and
p.ActiveFlag = 'Y'
-- #GroupList is created above this portion. That part works fine.
Poking at this for a while, I discover that you can't do a join and a multi-table query. I guess that makes sense (and I've never run into this before since I always use joins)... So, I change the query to inner join PersonType as well and everything is good.
Just thought this might save someone else 20 minutes of headscratching...