- CREATE PROCEDURE sp_Get_Member_xml
-
@MEMB_ID INT
-
AS
-
BEGIN
-
DECLARE @left_child_id INT,
-
@right_child_id INT,
-
@PARENT_MEMB_ID INT,
-
@MAIN_ID INT
-
-
DECLARE @temp TABLE
-
(
-
id BIGINT IDENTITY,
-
member_id INT,
-
member_name VARCHAR(50),
-
left_child_id INT,
-
right_child_id INT,
-
left_child_updated INT,
-
right_child_updated INT,
-
open_tag VARCHAR(MAX) NULL,
-
close_tag VARCHAR(MAX) NULL
-
)
-
-
SET @PARENT_MEMB_ID = @MEMB_ID;
-
SET @MAIN_ID = @MEMB_ID;
-
-
INSERT INTO @temp
-
SELECT
-
acc.memb_id,
-
mem.name,
-
acc.left_id,
-
acc.right_id,
-
0,
-
0,
-
'<M00' + CAST(mem.memb_id AS VARCHAR) + '_' + mem.name + '>',
-
'</M00' + CAST(mem.memb_id AS VARCHAR) + '_' + mem.name + '>'
-
FROM account(NOLOCK) acc
-
INNER JOIN members(NOLOCK) mem
-
ON acc.memb_id = mem.memb_id
-
WHERE acc.memb_id = @MEMB_ID
-
-
SET @MEMB_ID = 0
-
SELECT TOP 1 @MEMB_ID = member_id FROM @temp WHERE left_child_updated = 0 OR right_child_updated = 0
-
-
WHILE(@MEMB_ID != 0)
-
BEGIN
-
-- Left Child Logic
-
-
SELECT @left_child_id = left_child_id FROM @temp WHERE member_id = @MEMB_ID AND left_child_updated = 0
-
SET @PARENT_MEMB_ID = @MEMB_ID;
-
-
WHILE(@left_child_id != 0)
-
BEGIN
-
INSERT INTO @temp
-
SELECT
-
acc.memb_id,
-
mem.name,
-
acc.left_id,
-
acc.right_id,
-
0,
-
0,
-
'<M00' + CAST(mem.memb_id AS VARCHAR) + '_' + mem.name + '>',
-
'</M00' + CAST(mem.memb_id AS VARCHAR) + '_' + mem.name + '>'
-
FROM account(NOLOCK) acc
-
INNER JOIN members(NOLOCK) mem
-
ON acc.memb_id = mem.memb_id
-
WHERE acc.memb_id = @left_child_id
-
-
UPDATE @temp SET left_child_updated = 1 WHERE member_id = @PARENT_MEMB_ID
-
-
SET @PARENT_MEMB_ID = @left_child_id
-
SELECT @left_child_id = left_child_id FROM @temp WHERE member_id = @left_child_id AND left_child_updated = 0
-
END
-
UPDATE @temp SET left_child_updated = 1 WHERE member_id = @PARENT_MEMB_ID
-
-
-- Right Child Logic
-
-
SELECT @right_child_id = right_child_id FROM @temp WHERE member_id = @MEMB_ID AND right_child_updated = 0
-
SET @PARENT_MEMB_ID = @MEMB_ID;
-
-
WHILE(@right_child_id != 0)
-
BEGIN
-
INSERT INTO @temp
-
SELECT
-
acc.memb_id,
-
mem.name,
-
acc.left_id,
-
acc.right_id,
-
0,
-
0,
-
'<M00' + CAST(mem.memb_id AS VARCHAR) + '_' + mem.name + '>',
-
'</M00' + CAST(mem.memb_id AS VARCHAR) + '_' + mem.name + '>'
-
FROM dbo.account(NOLOCK) acc
-
INNER JOIN dbo.members(NOLOCK) mem
-
ON acc.memb_id = mem.memb_id
-
WHERE acc.memb_id = @right_child_id
-
-
UPDATE @temp SET right_child_updated = 1 WHERE member_id = @PARENT_MEMB_ID
-
-
SET @PARENT_MEMB_ID = @right_child_id
-
SELECT @right_child_id = right_child_id FROM @temp WHERE member_id = @right_child_id AND right_child_updated = 0
-
END
-
UPDATE @temp SET right_child_updated = 1 WHERE member_id = @PARENT_MEMB_ID
-
-
Print '2 - ' + CAST(@PARENT_MEMB_ID AS VARCHAR)
-
-
SET @MEMB_ID = 0
-
SELECT TOP 1 @MEMB_ID = member_id FROM @temp WHERE left_child_updated = 0 OR right_child_updated = 0
-
END
-
-
SELECT * FROM @temp
-
END
0 1015 Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ralph |
last post by:
HTTP 500 - Internal server error ASP pages
I am running Windows 2K with IIS 5.1 and I started having problems
accessing ASP databases shortly after applying the MS patch Windows
2000 Hotfix...
|
by: |
last post by:
I am accessing the same error-containing ASP page on an ISP server using w2k
IE6 but with different effect.
On the first computer I get several line of HTML outputed by ASP, shown
correctly by...
|
by: quitaxe |
last post by:
hi
i have tomcat 3.3,jdk1.4 setup and running perfectly on my win 98 pc.i
have tried to install apache cocoon on this but have failed.i'm
getting the following error when i startup tomcat(after it...
|
by: Mike Ratliff |
last post by:
When I try to create a new asp project, I get a 500 Internal Server Error
trying to connect to my local IIS server. I am running xp and vb.net 2003.
I added the msde database and I do not know if...
|
by: William F. Robertson, Jr. |
last post by:
I am having problems with using the WebRequest object (or HttpWebRequest ).
I have created the WebRequest and set the credentials, but when I call
GetResponse() it is throwing an internal server...
|
by: AlanS |
last post by:
I have used Visual Studio for about 8 months. I have developed some ASP solutions. I had to get on with some other things and have not worked with ASP.NET for a couple months. In the meantime, I...
|
by: Rod |
last post by:
I have been working with ASP.NET 1.1 for quite a while now. For some
reason, opening some ASP.NET applications we wrote is producing the
following error message:
"The Web server reported...
|
by: jf li |
last post by:
I have a Asp.net web application and a Asp.net Web service application. The
Web application is using HtmlInputFile to get a 50M size of file selected by
end user, read the data of this file and...
|
by: guillaume.braux |
last post by:
Hello,
I am running WS2008 + IIS7 + FASTCGI + ZendCore.
I have not modified the default ZendCore php.ini configuration file.
Actualy, any kind of PHP error, warning or notice gives me...
|
by: saipavan |
last post by:
Please go through the following fix this error.
1. Click on http://www.athensys.com/giorgio/search_gallery.php
and enter any keyword (or) word in the main search bar " Search here for Google...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
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,...
|
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...
| |