Stored Procedure and multiple keys which could be NULL

[Gaz]

I have a table which has 10 columns which make up the secondary key. 1
or more of these columns can be set but the remaining columns in the
secondary key will be null. For example :
id k1 k2 k3 k4 k5 k6 k7 k8 k9 k10 data
-------------------------------------------------
0 1 1 - - - - - - - - test0
1 1 1 1 - - - - - - - test1
2 1 1 2 - - - - - - - test2
3 1 1 3 - - - - - - - test3
4 1 1 3 1 - - - - - - test4
5 1 2 1 - - - - - - - test5
6 1 2 2 - - - - - - - test6

Each row represents a node in a tree structure, the secondary key
columns point to which node in the tree. The above example has one
node in tree branch 1, three nodes in tree branch 1-1, one node in
branch 1-1-3 and two nodes in branch 1-2.

My question is how can I write a single stored procedure to return
only the nodes in a given tree branch without needing logic based on
looking for NULL parameters. I have written the stored procedure
below. If I want to retrieve all nodes in branch 1-1 (ie; test1, test2
and test3) then I want be able to call :

execute "mysp Test" 1, 1

But this only returns a single record, test0. The reason for this is
obvious if you look at the stored procedure. What I really need is way
of saying

execute "mysp Test" 1, 1, NOT NULL

so that it returns all records in branch 1-1 where Key3 is NOT NULL
but all subsequent keys are null, ie; Key4-Key10 are NULL. The stored
procedure must work with any number of secondary keys though, so I
could use it to call :

execute "mysp Test" 1, 1, 3, NOT NULL

which would retrieve test4.

Can anybody help me out here?

Thanks for reading.
Here's the stored procedure as it stands :

CREATE PROCEDURE [dbo].[mysp Test]
( @key1 smallint,
@key2 smallint=NULL,
@key3 smallint=NULL,
@key4 smallint=NULL,
@key5 smallint=NULL,
@key6 smallint=NULL,
@key7 smallint=NULL,
@key8 smallint=NULL,
@key9 smallint=NULL,
@key10 smallint=NULL ) AS

select * from TreePositionTest
where
key1=@key1 and
key2=@key2 and
key3=@key3 and
key4=@key4 and
key5=@key5 and
key6=@key6 and
key7=@key7 and
key8=@key8 and
key9=@key9 and
key10=@key10
GO

[--CELKO--]

Get a copy of TREES & HIERARCHIES IN SQL for several other methods that
model trees. I think you will find that the nested sets model will run
a order of magnitude faster as your tree grows.

[oopaevah]

I have since had a rethink and have decided to have a ParentId column
which links nodes to the parent branch. I will also have a Sequence
column to provide ordering of nodes. When inserting or deleting nodes I
will have to update the subsequent nodes' Sequence column (although for
insertions I could stagger the Sequence number so I only have to update
the next node if the sequences overlap) but it will only be for that
particular branch so it won't be a problem as I don't envisage more
than ten nodes per branch although even a few hundred still won't be a
problem.

--CELKO-- wrote:

Get a copy of TREES & HIERARCHIES IN SQL for several other methods that model trees. I think you will find that the nested sets model will run a order of magnitude faster as your tree grows.

[Erland Sommarskog]

Gaz (oo******@yahoo.co.uk) writes:

My question is how can I write a single stored procedure to return
only the nodes in a given tree branch without needing logic based on
looking for NULL parameters. I have written the stored procedure
below. If I want to retrieve all nodes in branch 1-1 (ie; test1, test2
and test3) then I want be able to call :

execute "mysp Test" 1, 1

But this only returns a single record, test0. The reason for this is
obvious if you look at the stored procedure.
Hm, your procedure should not really return anything at all. Obviously
you are running with ANSI_NULLS off, and that gives you a single row.
But ANSI_NULLS is a legacy option, and you should be running with it
ON.
What I really need is way of saying

execute "mysp Test" 1, 1, NOT NULL

so that it returns all records in branch 1-1 where Key3 is NOT NULL
but all subsequent keys are null, ie; Key4-Key10 are NULL. The stored
procedure must work with any number of secondary keys though, so I
could use it to call :
You will have to use some value as a wild card, for instance -1.
Here's the stored procedure as it stands :

CREATE PROCEDURE [dbo].[mysp Test]
( @key1 smallint,
@key2 smallint=NULL,
@key3 smallint=NULL,
...
@key10 smallint=NULL ) AS

select * from TreePositionTest
where
key1=@key1 and
key2=@key2 and
key3=@key3 and
...
key10=@key10
GO

SELECT * FROM TreePositionTest
WHERE (key1 = @key1 OR key1 IS NULL AND @key1 IS NULL OR @key1 = -1)
-- etc.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp