Looping through tables and stuff....

[palagrim]

Good afternoon, I was wondering if anyone could help me with a problem before I stick my fist through my monitor?

Table:

HEADING | URL | ACCOUNT_TYPE
Blah

http://1

Type 1
Blah2

http://2

Type 1
Blah3

http://3

Type 2
Blah4

http://4

Type 3
Blah5

http://5

Type 3

The idea is to produce a dynamic menu for an intranet where menu elements are produced depending on the account type. I can't use a basic WHERE clause because some accounts are multiple types (you can be account_type 1 and 3 for example.)

I'm trying to pass the various account types as a string from ASP to my stored procedure as a comma delimited string ie. 'type1, type2' and can split this using a split function so i end up with another table:

INDEX VALUE
0 Type1
1 Type2

I THINK I need to loop through this second table and then perfom a select/insert type thing with a where clause for each value...

And i'm getting myself all tied up in knots... Does anybody have suggestions? I'm no good at SQL (complete newbie really) so any help would be cool.

There may be cake and iced buns! ;-)

Thank you.

 

[gmmastros]

There should be no reason to loop through your tables.

First, I'll create a table variable to mimic your real table. Then, I'll show you how you can inner join with the table coming from the split function to your real table.

[green]-- Creating a 'mimic' of your real table[/green]
Declare @Temp Table(Heading VarChar(100), URL VarChar(100), ACCOUNT_TYPE VarChar(100))

Insert Into @Temp Values('Blah' ,'[URL unfurl="true"]http://1','Type[/URL] 1')
Insert Into @Temp Values('Blah2','[URL unfurl="true"]http://2','Type[/URL] 1')
Insert Into @Temp Values('Blah3','[URL unfurl="true"]http://3','Type[/URL] 2')
Insert Into @Temp Values('Blah4','[URL unfurl="true"]http://4','Type[/URL] 3')
Insert Into @Temp Values('Blah5','[URL unfurl="true"]http://5','Type[/URL] 3')

[green]-- Setting up the inputs[/green]
Declare @Input VarChar(8000)
Set @Input = 'Type 1,Type2'

[green]-- Getting the inputs in to a table.[/green]
Declare @Inputs Table(Account_Type VarChar(100))

[green]-- using the inputs table to get the data[/green]
Select  T.Heading, T.URL, T.ACCOUNT_TYPE
From    [red]@Temp[/red] T
        Inner Join @Inputs I On T.Account_Type = I.Account_Type

You'll need to use your real table instead of my @Temp table (appearing in [red]red[/red]).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[palagrim]

Works like a charm! Thanks George!

 

[gmmastros]

No problem. I'm glad to have helped.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom