I have three tables - REGIONS, AGENTS, MANAGERS.
From a list of queryTERMS (comma separated list), I want to be able
to retrieve matches for REGIONS, AGENTS or MANAGERS. The heirarchy
is Managers-->Agents-->Regions.
A foreign key in REGIONS relates REGIONS to the Primary Key in
AGENTS. A foreign key in AGENTS relates AGENTS to the Primary Key in
MANAGERS.
Thanks in advance for the help!
The Stored Procedure is:

ALTER procedure GetLists
@queryTerms varchar(600)
as

-- create terms temp table
CREATE TABLE #terms(term varchar(50))


-- parse the query terms passed
declare @start int
declare @end int
declare @term varchar(20)

set @start=1
set @end=1

set @end=CHARINDEX(',',@queryTerms,@start)
delete from #terms

while @end<>0
begin
set @term=SUBSTRING(@queryTerms,@start,@end-@start)

insert into #terms values(rtrim(ltrim(@term)))

set @start=@end+1
set @end=CHARINDEX(',',@queryTerms,@start)
end

if @start=1
insert into #terms values(SUBSTRING(@queryTerms,@start,len
(@queryTerms)))
else
insert into #terms values(SUBSTRING(@queryTerms,@start,len
(@queryTerms)-@start+1))


declare @newcount int
declare @oldcount int
CREATE TABLE #allterms(term varchar(50))

insert into #terms exec GetManagerList
insert into #terms exec GetAgentList
insert into #terms exec GetRegionList

insert into #allterms select distinct term from #terms
delete from #terms

select @newcount=count(*) from #allterms
set @oldcount=-1

while @newcount<>@oldcount
begin
set @oldcount=@newcount
insert into #terms select term from #allterms

insert into #terms exec GetManagerList
insert into #terms exec GetAgentList
insert into #terms exec GetRegionList

delete #allterms
insert into #allterms select distinct term from #terms
delete from #terms
select @newcount=count(*) from #allterms

print cast(@oldcount as varchar(10))+','+cast(@newcount as
varchar(10))
end

--select distinct term from #terms

SELECT distinct dbo.Managers.ManagerName
FROM dbo.Agents INNER JOIN
dbo.Managers ON dbo.Agents.MgID = dbo.Managers.MgID INNER
JOIN
dbo.Regions ON dbo.Agents.AgID = dbo.Regions.AgID, #allterms
WHERE dbo.Agents.AgentName=#allterms.term or
dbo.Managers.ManagerName=#allterms.term or
dbo.Regions.Region=#allterms.term

SELECT distinct dbo.Agents.AgentName
FROM dbo.Agents INNER JOIN
dbo.Managers ON dbo.Agents.MgID = dbo.Managers.MgID INNER
JOIN
dbo.Regions ON dbo.Agents.AgID = dbo.Regions.AgID, #allterms
WHERE dbo.Agents.AgentName=#allterms.term or
dbo.Managers.ManagerName=#allterms.term or
dbo.Regions.Region=#allterms.term

SELECT distinct dbo.Regions.Region
FROM dbo.Agents INNER JOIN
dbo.Managers ON dbo.Agents.MgID = dbo.Managers.MgID INNER
JOIN
dbo.Regions ON dbo.Agents.AgID = dbo.Regions.AgID, #allterms
WHERE dbo.Agents.AgentName=#allterms.term or
dbo.Managers.ManagerName=#allterms.term or
dbo.Regions.Region=#allterms.term

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 12:15 AM 15/04/2005 +0000, you wrote:
>
>
>
> >How can I convert and MS SQL SP that uses temoporary tables

#temp to

> >FireBird? Thanks!
>
> It totally depends on what the temp table is being used for. For

example,

> if it is to generate a set to be returned to the client, you would

write a

> selectable stored procedure. If it is to create a set that some

external

> query has to read, you would create a "permanent temporary table"

with

> identifiers for the transaction ID and any other attributes of the

set that

> are needed to identify the set and have the SP insert its rows

into that

> table. There are other possibilities, too.
>
> If none of this makes sense to you, how about providing some

simplified

> pseudocode that illustrates what your MSSQL procedure does? The

closer we

> can get to the problem, the closer you will get to a solution.
>
> ./hb