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 TreePositionTes t
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 3 1758
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.
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.
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 TreePositionTes t where key1=@key1 and key2=@key2 and key3=@key3 and ... key10=@key10 GO
SELECT * FROM TreePositionTes t
WHERE (key1 = @key1 OR key1 IS NULL AND @key1 IS NULL OR @key1 = -1)
-- etc.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dave |
last post by:
I came across an article in SQL Mag about Crosstab Queries. It works
great in Query Analyzer, but I'm stuck on how to use it in an Access
ADP. I need to use it as a Recordsource in a form and report. Can
someone tell me how to use it, and please try to be as descriptive as
possible. I'm new to Stored Procedures.
Thanks
*****************************************
|
by: Dino L. |
last post by:
How can I run stored procedure (MSSQL) ?
|
by: John |
last post by:
Hi
When developing vb.bet winform apps bound to sql server datasource, is it
preferable to use SELECTs or stored procedure to read and write data from/to
SQL Server? Why?
Thanks
Regards
|
by: Dabbler |
last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the
following error when trying to update (ExecuteNonQuery):
System.Data.SqlClient.SqlException: Procedure or Function 'UpdateRegistrant'
expects parameter '@EMail', which was not supplied.
The field value was null in the database and not changed in the FormView so
is null going back into the stored procedure. I'm stumped and would greatly
appreciate any suggestions.
|
by: deepdata |
last post by:
Hi,
I am trying to fetch data from db2 (express version) database by
calling stored procedure. I have tried to use both cursor and for loop
but still i am getting error.
--======Start procedure=============
Create PROCEDURE get_timedout_scripts
(
| |
by: karups |
last post by:
Hi,
I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select any number of items in listbox, I have to take
value from items and
pass it to stored procedure to extract a dataset back.
1.What should i do while passing the multiple selected values?
2.Can i use 'in' clause in SQL procedure like
|
by: nishi57 |
last post by:
I hope I can get some help regarding this issue, which has been going
on for a while. I have a desktop user who is having problem running
"Stored Procedures". The DB2 Connect application works fine but when he
runs the stored procedure, he gets the following error message.
"SYSPROC".CSGCSB54 - Run started.
Data returned in result sets is limited to the first 100 rows.
Data returned in result set columns is limited to the first 20...
|
by: yin_n_yang74 |
last post by:
I am new to SQL and SQL Server world. There must be a simple solution
to this, but I'm not seeing it. I am trying to create a crystal
report (v8.5) using a stored procedure from SQL Server (v2000) in
order to report from two databases and to enable parameters.
When I create the stored procedure, it joins multiple one-to-many
relationship tables. This results in repeated/duplicate records. Is
this an issue that should be solved within...
|
by: gamaz |
last post by:
Hi,
I am trying to work on a stored procedure that will work with multiple database. I have a prototype of multiple databases. Those are named as the following: ts2_aldkm_app, ts2_aldkp_app, ts2_aldkt_app. The middle part of the database name corresponds to the site name e.g aldkm corresponds to site aldkm etc. Each database has one table tblCustomer which is scripted as follows:
if exists (select * from dbo.sysobjects where id =...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |