SQL question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL question

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2011-03-23 : 08:03:15

I know this seems a bit strange, but the sql that I put here is part of 3 unions. I have to add a column and have that column be part of the search criteria, but I get this error: Invalid column name typeDesc


SELECT     dbo.Offender.SID, dbo.Offender.OffenderID, dbo.Offender.FirstName, dbo.Offender.MiddleName, dbo.Offender.LastName, 
                      SPGlobalLookups.dbo.County_LKP.County_Name, dbo.OffenderEmployment.Street, dbo.OffenderEmployment.Street2, dbo.OffenderEmployment.City, 
                      dbo.OffenderEmployment.Zip, 'Employer' AS typeDesc
FROM         dbo.Offender INNER JOIN
                      dbo.OffenderEmployment ON dbo.Offender.OffenderID = dbo.OffenderEmployment.OffenderId AND typeDesc IN (@AddressTypeID) AND 
                      dbo.OffenderEmployment.County_Code IN (@CountyCode) INNER JOIN
                      SPGlobalLookups.dbo.County_LKP ON dbo.OffenderEmployment.County_Code = SPGlobalLookups.dbo.County_LKP.County_Code
WHERE     (dbo.Offender.OffenderStatus = 1)
ORDER BY SPGlobalLookups.dbo.County_LKP.County_Name, typeDesc, dbo.Offender.LastName, dbo.Offender.FirstName

Dave
Helixpoint Web Development
http://www.helixpoint.com

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-23 : 08:11:26

I copied the code to an SSMS window and it parses without any errors for me. So I have nothing to suggest on that front. But, the reason I am responding to your posting is because of this line in the code:

dbo.OffenderEmployment.County_Code IN (@CountyCode)

If @CountyCode has code for a single country it would work fine, but if the intention was to send a comma-separated list of country codes, that would not work as one might expect.

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2011-03-23 : 08:53:20

This works

AND 'Employer' IN ( @AddressTypeID )

Dave
Helixpoint Web Development
http://www.helixpoint.com

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-23 : 09:35:42

But it will break if @AddressTypeID - '1,2,3,4' or some such. Why not Employer = @AddressTypeID

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources