looping 1

[WarcraftPlayer]

I know I would do this with code but i was hoping I could do it in a procedure.

I have a table

TableID Payer State EffectiveDate ExpirationDate
1 MC FL 7/28/2004 7/28/2005
2 RC TX 8/26/2004 3/28/2005
3 M TN 3/28/2004 12/28/2005
4 MC FL 6/28/2004 6/28/2005

Sometimes Ill have a need to query by mulitiple states maybe 25 of them. So instead of doing a query like this

Select * from Table where State = xx

and have the code loop through it. I was hoping to pass a string of states in say "FL,TX,MD,TN,AR". Split that up and loop through but I cant seem to figure out how to make it work.
Thanks for any help.

 

[JayKusch]

DECLARE @PassedStates VarChar(300)
DECLARE @SQL_Command VarCHar(4000)

SET @SQL_Command = 'Select * from Table where State IN (' + RTRIM(LTRIM(@PassedStates)) + ')'

EXEC (@SQL_Command)


Thanks

J. Kusch

 

[WarcraftPlayer]

That would work only thing is I cant use the I cant use the @SQL_Command. it has to be without building the string so something like this but I am having trouble passing it in.

@PassedStates Varchar (300)

Select * From Table Where State IN(@PassedStates)

 

[mrdenny]

If you can't use dynamic SQL you'll have to create a temp table or table variable, use substring, and patindex to break apart the string variable you pass in into it's parts, loading each part into a record in the temp table. Then do a standard IN statement with a subquery as the value. Something like this.

create proc as YourProc
    @PassedStates as varchar(1000)
as
declare @TempTable table
(State varchar(2))

/*Code to break apart @PassedStates and insert it into @TempTable*/

select *
from Table
where State in (select State from @TempTable)
go

I'll leave making the code to break apart @PassedStates to you. I can't take all the fun away.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[karluk]

The following query should return the rows you want.

Select * From Table
   where CharIndex(State, @PassedStates) > 0

 

[JamesLean]

Also, faq183-5207.

--James