Dynamic Filtering - 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 2005 Forums

Transact-SQL (2005)

Dynamic Filtering

Author

Topic

missMac
Posting Yak Master

124 Posts

Posted - 2008-10-23 : 17:50:52

Hello,
we have a table that does some intelligent routing. I cant seem to get the hang of it.

pls help.


 set @country = (select DefaultCountry from main_credits where username = @username)
 
 set @cc_count = (select COUNT(*) from route_main where Batch = @id)

while @cc_count >= 1
 
 if @cc_count >=2 -- if its 1, then run the loop only once. Meaning there is one country
begin
 set @cclist = @country+','+@country
set @country = (select distinct top (1) country from route_main  where Country not in (@cclist))


set @cc_count = @cc_count - 1
end

-- I want to filter the country list. Ie UK,USA,canada,malta

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-23 : 19:46:36

can you explain the business rules please?
Looks like you are selecting a list of countries depends on the number of batches.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 01:43:10

it seems like you're trying to build a comma seperated list of countries. no need to loop for that. just use below

select @country =left(cl.cntrylist,len(cl.cntrylist)-1)
from (select country +','
      from
        (select DefaultCountry as country 
         from main_credits 
         where username = @username
         union
         select country from route_main           
         )t
       for xml path(''))cl(cntrylist)

missMac
Posting Yak Master

124 Posts

Posted - 2008-10-24 : 02:39:02

Great it works.

But is it possible to loop through the values one by one and assign them to a variable. ie if the values are

UK,USA,canada,malta

then run a loop

while (@country is not null)
begin
select @t = --loop thru each csv one by one
print @t
end

Is this possible ? thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 03:55:43

quote:
Originally posted by missMac

Great it works.

But is it possible to loop through the values one by one and assign them to a variable. ie if the values are

UK,USA,canada,malta

then run a loop

while (@country is not null)
begin
select @t = --loop thru each csv one by one
print @t
end

Is this possible ? thanks

its possible . any special reason why you dont want to use solution above?

missMac
Posting Yak Master

124 Posts

Posted - 2008-10-24 : 04:11:07

Yes, the solution is ok. It gets out a csv value.

Step2, I want to be able to select each value and loop through it.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-10-24 : 06:29:08

DECLARE @columnVal VARCHAR(50)
DECLARE @country VARCHAR(10)
SET @columnVal= 'UK,AUS,PK,IND'

DECLARE @INDEX INT
SET @INDEX = 0

WHILE (@INDEX < LEN (@columnVal))
begin
SELECT @country = SUBSTRING(@columnVal,@INDEX,(CASE WHEN CHARINDEX(',',@columnVal,@INDEX)=0 THEN LEN(@columnVal)ELSE CHARINDEX(',',@columnVal,@INDEX) END )-@INDEX)
SET @INDEX = CASE WHEN CHARINDEX(',',@columnVal,@INDEX)=0 THEN LEN(@columnVal)ELSE CHARINDEX(',',@columnVal,@INDEX) END+1
print @country

end

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-24 : 06:39:12

quote:
Originally posted by missMac

Yes, the solution is ok. It gets out a csv value.

Step2, I want to be able to select each value and loop through it.

This is sounding less and less like a task you should be asking a database server to do.

What's your end goal here?

Regards,

-------------
Charlie

missMac
Posting Yak Master

124 Posts

Posted - 2008-10-24 : 07:15:25

quote:
Originally posted by Transact Charlie

quote:
Originally posted by missMac

Yes, the solution is ok. It gets out a csv value.

Step2, I want to be able to select each value and loop through it.

This is sounding less and less like a task you should be asking a database server to do.

What's your end goal here?

Regards,

-------------
Charlie

We have a routing system implemented based on country formats. And each insert has to be based on the country.

So each entry into our table has to be categorized based on a dynamic value.

So if we have 1,000 UK entries. then It has to be filtered based on the country, other countries have to follow suite. Before being passed into the final table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources