SP returning multiple resultsets

29 Sep 2005 1:45 PM

Henrik Skak Pedersen

Hello,

I have a stored procedure which is traversing a tree. The table looks like
this: Organization (OrganizationId, ParentOrganizationId, ect....). The
stored procedure is looking like this:

ALTER PROCEDURE dbo.ShowHierarchy
(
@Root varchar(10) = NULL
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @OrganizationId varchar(10)

if (@Root IS NOT NULL)
BEGIN
SELECT * FROM Organization WHERE OrganizationId = @Root
SET @OrganizationId = (SELECT MIN(OrganizationId) FROM Organization WHERE
ParentOrganizationId = @Root)
END
ELSE
SET @OrganizationId = (SELECT MIN(OrganizationId) FROM Organization WHERE
ParentOrganizationId IS NULL)

-- Loop
WHILE @OrganizationId IS NOT NULL
BEGIN
EXEC dbo.ShowHierarchy @OrganizationId
SET @OrganizationId = (SELECT MIN(OrganizationId) FROM Organization WHERE
ParentOrganizationId = @Root AND OrganizationId > @OrganizationId)
END
END

It is actually working pretty well when I am printing the values of the
different organizational units. My problem is that the sp is returning a
result sets pr. transaction, because of the following line: SELECT * FROM
Organization WHERE OrganizationId = @Root

I would like it to return only one result set containing all transactions.
Can anybody please help me?

Kind regards

Henrik Skak Pedersen.

29 Sep 2005 1:55 PM

SQL

Rewrite the SQL or use a temp table for the results and do a select after you
are completely done

http://sqlservercode.blogspot.com/

Show quote

"Henrik Skak Pedersen" wrote:

> Hello,
>
> I have a stored procedure which is traversing a tree. The table looks like
> this: Organization (OrganizationId, ParentOrganizationId, ect....). The
> stored procedure is looking like this:
>
> ALTER PROCEDURE dbo.ShowHierarchy
> (
> @Root varchar(10) = NULL
> )
> AS
> BEGIN
> SET NOCOUNT ON
>
> DECLARE @OrganizationId varchar(10)
>
> if (@Root IS NOT NULL)
> BEGIN
>   SELECT * FROM Organization WHERE OrganizationId = @Root
>   SET @OrganizationId = (SELECT MIN(OrganizationId) FROM Organization WHERE
> ParentOrganizationId = @Root)
> END
> ELSE
>   SET @OrganizationId = (SELECT MIN(OrganizationId) FROM Organization WHERE
> ParentOrganizationId IS NULL)
>
> -- Loop
> WHILE @OrganizationId IS NOT NULL
> BEGIN
>   EXEC dbo.ShowHierarchy @OrganizationId
>   SET @OrganizationId = (SELECT MIN(OrganizationId) FROM Organization WHERE
> ParentOrganizationId = @Root AND OrganizationId > @OrganizationId)
> END
> END
>
> It is actually working pretty well when I am printing the values of the
> different organizational units. My problem is that the sp is returning a
> result sets pr. transaction, because of the following line: SELECT * FROM
> Organization WHERE OrganizationId = @Root
>
> I would like it to return only one result set containing all transactions.
> Can anybody please help me?
>
> Kind regards
>
> Henrik Skak Pedersen.
>
>
>

29 Sep 2005 3:39 PM

Henrik Skak Pedersen

Thank you very much for your reply. How should I do that?

Show quote

"SQL" <S**@discussions.microsoft.com> wrote in message
news:DAF93149-6616-4E7D-B1CC-AD64B609E051@microsoft.com...
> Rewrite the SQL or use a temp table for the results and do a select after
> you
> are completely done
>
> http://sqlservercode.blogspot.com/
>
> "Henrik Skak Pedersen" wrote:
>
>> Hello,
>>
>> I have a stored procedure which is traversing a tree. The table looks
>> like
>> this: Organization (OrganizationId, ParentOrganizationId, ect....). The
>> stored procedure is looking like this:
>>
>> ALTER PROCEDURE dbo.ShowHierarchy
>> (
>> @Root varchar(10) = NULL
>> )
>> AS
>> BEGIN
>> SET NOCOUNT ON
>>
>> DECLARE @OrganizationId varchar(10)
>>
>> if (@Root IS NOT NULL)
>> BEGIN
>>   SELECT * FROM Organization WHERE OrganizationId = @Root
>>   SET @OrganizationId = (SELECT MIN(OrganizationId) FROM Organization
>> WHERE
>> ParentOrganizationId = @Root)
>> END
>> ELSE
>>   SET @OrganizationId = (SELECT MIN(OrganizationId) FROM Organization
>> WHERE
>> ParentOrganizationId IS NULL)
>>
>> -- Loop
>> WHILE @OrganizationId IS NOT NULL
>> BEGIN
>>   EXEC dbo.ShowHierarchy @OrganizationId
>>   SET @OrganizationId = (SELECT MIN(OrganizationId) FROM Organization
>> WHERE
>> ParentOrganizationId = @Root AND OrganizationId > @OrganizationId)
>> END
>> END
>>
>> It is actually working pretty well when I am printing the values of the
>> different organizational units. My problem is that the sp is returning a
>> result sets pr. transaction, because of the following line: SELECT *
>> FROM
>> Organization WHERE OrganizationId = @Root
>>
>> I would like it to return only one result set containing all
>> transactions.
>> Can anybody please help me?
>>
>> Kind regards
>>
>> Henrik Skak Pedersen.
>>
>>
>>

29 Sep 2005 7:25 PM

Hugo Kornelis

On Thu, 29 Sep 2005 17:39:05 +0200, Henrik Skak Pedersen wrote:

(snip top-posting)

>>> My problem is that the sp is returning a
>>> result sets pr. transaction, because of the following line: SELECT *
>>> FROM
>>> Organization WHERE OrganizationId = @Root
>>>
>>> I would like it to return only one result set containing all
>>> transactions.
>>
>> Rewrite the SQL or use a temp table for the results and do a select after
>> you
>> are completely done
>
>Thank you very much for your reply. How should I do that?

Hi Henrik,

The second idea (use a temp table) has basically this structure:

-- Make temp table
CREATE TABLE #Temp
            (Col1 ....,
             Col2 ....,
             ......)
-- Store the results of two SELECT statements in the temp table
INSERT INTO #Temp (Col1, Col2, ...)
SELECT ....
INSERT INTO #Temp (Col1, Col2, ...)
SELECT ....
-- Show the contents of the temp table
SELECT Col1, Col2, ...
FROM   #Temp
-- Drop the temp table
DROP TABLE #Temp

I'm not a big supporter of the first idea (rewriting SQL) - unless you
change the data model as well. The data model you chose to store your
hierarchy in is called the "adjacency list model". It's very intuitive,
since it's close to how humans perceive hierarchies. But it's not easy
to manipulate in relational queries. A better model would be the "nested
set model". If you google for "nested set model" + celko, you will find
lots of explanations. Or buy "Joe Celko's Trees and Hierarchies in SQL
for Smarties", by Joe Celko.

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)