IN Clause Parameters for 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)

IN Clause Parameters for Stored Procedures

Author

Topic

derketo
Starting Member

28 Posts

Posted - 2005-01-05 : 11:50:05

I'm passing some zip codes to a stored procedure but it doesn't seem to be looking up the zip codes. I pass the parameter back out and it's the same as I put it in. Whenever I just put the zip codes in there without using the parameter, it looks it up, though. Anyone have any suggestions?

CREATE PROCEDURE [dbo].[spQuickSearch]

@ParmAllValidZips varchar(8000),
@ParmZips varchar(8000) = null output

AS

select Username from Members
where ZipCode IN (@ParmAllValidZips)

set @ParmZips = @ParmAllValidZips
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-05 : 11:58:35

comma separated?

select Username from Members
where ',' + @ParmAllValidZips + ',' like '%,' + ZipCode + ',%'

or

declare @sql varchar(4000)
select @sql = 'select Username from Members where ZipCode IN (' + @ParmAllValidZips + ')'
exec (@sql)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derketo
Starting Member

28 Posts

Posted - 2005-01-05 : 12:18:34

Thanks, nr. You're a life saver. Worked like a charm.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources