Problems with IN clause in Stored Procedures

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 2000 Forums

SQL Server Development (2000)

Problems with IN clause in Stored Procedures

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-07 : 08:59:06

John writes "Hey, guys.
I'm passing a long list of zip codes to a stored procedure. The IN clause doesn't seem to be working, though. I've tried running it back out and I get the same results I put in but it's not finding any records with the correct zip codes. Here's the code. Help!

CREATE PROCEDURE [dbo].[spQuickSearch]

@ParmGender bit,
@ParmSeeking bit,
@ParmYoungestDOB smalldatetime,
@ParmOldestDOB smalldatetime,
@ParmMinPhotos int,
@ParmTEthnicity nvarchar(50),
@ParmTReligion nvarchar(50),
@ParmAllValidZips varchar(8000),
@ParmZips varchar(4000) = null output

AS

if @ParmTEthnicity = 'Any'
begin
set @ParmTEthnicity = null
end

if @ParmTReligion = 'Any'
begin
set @ParmTReligion = null
end

select Username from Members LEFT OUTER JOIN Vitals on Members.MembersID = Vitals.MembersID
where Gender = @ParmGender AND Seeking = @ParmSeeking AND DOB BETWEEN @ParmOldestDOB AND @ParmYoungestDOB AND
@ParmMinPhotos >= (select COUNT(MemberPhotosID) from MemberPhotos where MembersID = Members.MembersID AND Private = 0 AND
ApprovalCode = 1) AND MEthnicity = COALESCE(@ParmTEthnicity,MEthnicity) AND MReligion = COALESCE(@ParmTReligion,MReligion) AND
ZipCode IN (@ParmAllValidZips)

set @ParmZips = @ParmAllValidZips
GO

Any help is greatly appreciated.
John"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-07 : 09:43:06

seems that people are to lazy to search this site or don't know how... i wonder which one is it...
but the number of times this question was asked...phew...
http://www.sqlteam.com/item.asp?ItemID=11499

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources