looping filters - 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)

looping filters

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-09-27 : 07:01:53

hi

how do i do a looping filter.
For example i have 5 values in a variable called @PartNumber 5,2,10,6,8,1

The final result of 1 is based on the result of 8 and the result of 8 is based on the result of 6 and so on.

My data is stored in such a single row like 20,6,2,10,1,8,100,30,25,65,10,5,44

I tried using Insert into #temp select * from TA where field1 like 5
and continue to use #Temp to filter again this time is 2 but failed.

How should i go about it? thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-09-27 : 08:09:13

Don't pass in the variable as a comma separated value? Pass it in as a table instead. Or BCP your text file into it's own table.
Any way you slice it, do NOT use a comma separated anything...

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-09-27 : 08:44:48

hi

I am not passing it as a comma seperated values. In fact it has been sliced into individual numbers. Any example of how i can loop thru every numbers? Thanks a lot.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-27 : 09:39:00

Wouldn't it be possible to have a query like this?

select * from TA
where ','+field1+',' like '%,5,%'
and ','+field1+',' like '%,2,%'
and ','+field1+',' like '%,10,%'
and ','+field1+',' like '%,6,%'
and ','+field1+',' like '%,8,%'
and ','+field1+',' like '%,1,%'

Can you give it a shot?

No, you're never too old to Yak'n'Roll if you're too young to die.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-09-27 : 12:16:38

if you have ONE variable, and it contains the value '5,2,10,6,8,1'
you have a comma separated list. search for the split function on this forum.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources