Stored Proc Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Stored Proc Help

Author

Topic

ksidj
Starting Member

11 Posts

Posted - 2002-06-18 : 15:54:13

Consider this table-

directorykey directoryname substructureof
1 \engineering\ <NULL>
2 group1\ 1
3 group2\ 1
4 group3\ 1
5 sub1\ 4
6 sub2\ 4

I'm using this Select statement-

SELECT d3.directoryname + d2.directoryname + d1.directoryname AS
full_path
FROM directory d1, directory d2, directory d3
WHERE d1.directorykey = 3 AND
d1.substructureof = d2.directorykey AND
d2.substructureof = d3.directorykey;

In the WHERE clause, if I set d1.directorykey >= 5 then I will get the path.

If in the WHERE clause, I set d1.directorykey < 5 I get nothing....and in my code I get all kinds of BOF or EOF are TRUE error messages.

This tells me that I might need to do some conditional stuff in the WHERE clause so that if someone chooses a group instead of a sub, I can still get the path info.

Can I do conditional stuff within a Select statement or do I need to figure out a way to do what I want in my code?

I'm also thinking that I need a Stored Proc that dynamically creates a query based on the directory key passed in, and how many iterations it takes to get to the top-level directory record.

Has anyone done this kinda stuff before? Any ideas to get me going?

Thanks!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2002-06-18 : 16:02:14

There are a lot of ways to deal with this. You could just test for the existence of a return set on your directory number first though. If there is a not exists, return a '' at the end of the procedure using a goto on the test. Be sure to have your code handle the blanks. Otherwise you will get errors, because it can't handle the return set.

One quick, dirty way to do it.

Derrick Leggett
derrick_leggett@hotmail.com

ksidj
Starting Member

11 Posts

Posted - 2002-06-18 : 16:10:20

I think this does what I will need it to do...although I hadn't exactly anticipated I'd have to get into it this deep.

http://www.sqlteam.com/item.asp?ItemID=8866

Any reasons I should pursue this solution?

TIA

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-06-18 : 16:58:48

1. You will never need to rewrite queries based on how deep you need to go.

2. Without using join after join your query will notice an increase in speed.

Your other option is to write a recurrsive stored procedure to return the results for you, but the single query using depth will be much faster. If you ever needed to insert into the tree set you could easily reorder the depth with a little logic.

I have come across a similar situation using depth and parents to display a message board without having to use multiple joins or recurssive procedures, using the depth functionality and it worked out pretty nicely.

Got SQL?

ksidj
Starting Member

11 Posts

Posted - 2002-06-19 : 16:26:02

Ok...I've got my table updated to show the depth and lineage, but i'm still not sure how to use it.

I'm joining my directory table to an uploaded files table. The ufiles table contains stuff like file name, file extension, size, create date, modify date, etc...

Here's my new directory table-

directorykey directoryname substructureof depth lineage
100 /root/ <NULL> 0 /
101 group1/ 100 1 /100/
102 group2/ 100 1 /100/
103 group3/ 100 1 /100/
104 sub1/ 103 2 /100/103/
105 sub2/ 103 2 /100/103/

I would like to be able to return full paths (and portions thereof)...and previous to adding depth and lineage I was trying to use this syntax-

SELECT d3.directoryname + d2.directoryname + d1.directoryname + U.fname
AS full_path
FROM ufiles U, directory d1, directory d2, directory d3
WHERE d1.directorykey = 105 AND
d1.substructureof = d2.directorykey AND
d2.substructureof = d3.directorykey;

The problem with this Select statement is that if I enter 101,102, or 103 in the WHERE d1.directorykey = clause I get no results.

Can someone help me with a sample Select statement to get me going?

That would be a huge help.

Thanks!

BTW- Windows 2000 SQL Server 7

ksidj
Starting Member

11 Posts

Posted - 2002-06-26 : 13:20:20

Still desperate for some help on this ^^^^

Can anyone help me get going with some syntax?

Thanks in advance.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-26 : 13:46:55

Sorry, what do you want the output to look like? Can you provide an example?

ksidj
Starting Member

11 Posts

Posted - 2002-06-26 : 15:11:44

I would like the out put to contain full path information and the file name.

file name would come from my ufiles table, and the path information would come from my directory table shown above.

The final result would look something like-

/root/group1/sub1/filename.txt

Thanks!!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-26 : 15:19:47

Why not just store the full path of the file name in your database, instead of structuring it using ID's and a hierarchy? You can modify the lineage column to accomplish this. You really can't use the ID's in the lineage column because you can't substitute the file name for its respective ID value.

ksidj
Starting Member

11 Posts

Posted - 2002-06-26 : 15:27:03

I guess it's my understanding that an adjaceny model would make my data more relational. No?

I can store full path information for each file- but it would be nice if the file name, file size, file type, created date, etc...was independent of it's location.

The same question applies to the organization chart- why can't I just store supervisor, boss, big boss data in each employee row?

I could....but would I really want to????

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-26 : 15:45:37

It's not the data or how you're storing it, it's how you want to display it. The other attributes are still independent of its path. You want to display the PATH, but you're storing the HIERARCHY/LINEAGE. They are not the exact same thing. If you were to display the data in a nested fashion:


/root/
      folder1/
              file1.txt
              file2.txt
      folder2/
              file2.txt
              file3.txt

Then you could do that using the lineage and depth, because you're only displaying one file name. Since you want to display the whole path:

/root/
/root/folder1/
/root/folder1/file1.txt
/root/folder1/file2.txt
/root/folder2/
/root/folder2/file2.txt
/root/folder2/file3.txt

The only way to do this without storing the names in the lineage column is to do a funky replace operation on it, and it will most likely require multiple passes through the data.

ksidj
Starting Member

11 Posts

Posted - 2002-06-26 : 16:04:49

quote:

If you were to display the data in a nested fashion:

/root/
folder1/
file1.txt
file2.txt
folder2/
file2.txt
file3.txt

Then you could do that using the lineage and depth, because you're only displaying one file name.

Can you show me the syntax for this SELECT?

My ultimate goal is to use the data on some ASP pages where the user can upload a file, and specify the subfolder and group under root.

I'm guessing I can always massage the data in VBScript...i'm just trying to sort out the best way to get the data in and out of SQL Server most efficiently and logically.

I need full path as in /root/group/sub/filename so that I can ultimately provide a link that the user can click on to download the file.

If it's better to store full path info with the file, I'll do that.

Thanks for the continued help on this....

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-26 : 16:32:37

SELECT Space(10*Depth) + DirectoryName AS Directory
FROM Directory
ORDER BY Lineage + DirectoryName

It might need some tweaking, but that's the general form.

ksidj
Starting Member

11 Posts

Posted - 2002-06-26 : 16:43:32

The ORDER BY clause puts the subs before the groups.

It shows up correctly if the ORDER BY is removed.

Soooo...I still need full path info in the lineage column?

Damn...just seems like there must be some really cool elegant way to construct the path and filename with the current schema...

Oh well...maybe not.

Thanks for all the help.

ToddV
Posting Yak Master

218 Posts

Posted - 2002-06-27 : 10:58:57

THis requires a loop, but this will do it:

Declare @DirectoryName Varchar(50),
@directorykey INT

SELECT @directorykey = 105
SELECT @DirectoryName = ''

WHILE 1=1
BEGIN
SELECT @DirectoryName = directoryname + @DirectoryName,
@directorykey = substructureof
FROM #TEMP
WHERE directorykey = @directorykey

-- SELECT "@DirectoryName" = @DirectoryName,
-- "@directorykey" = @directorykey
IF @directorykey IS NULL
BREAK

END

SELECT @DirectoryName

ksidj
Starting Member

11 Posts

Posted - 2002-06-27 : 12:05:42

This is interesting, but it doesn't seem to do anything.

It returns with "1 row effected" but no results show up or anything...

Can I get some additional clarification on what this is doing, or how I should be using it?

Thanks a million!

ToddV
Posting Yak Master

218 Posts

Posted - 2002-06-27 : 12:11:44

This returns the full path for a given Directory Key. Is that not what you need. Here is the test data I used:

SELECT directorykey,
directoryname,
substructureof
INTO #TEMP
FROM (SELECT 100 as "directorykey", '/root/' as "directoryname", NULL as "substructureof"
UNION
SELECT 101, 'group1/', 100
UNION
SELECT 102, 'group2/', 100
UNION
SELECT 103, 'group3/', 100
UNION
SELECT 104, 'sub1/', 103
UNION
SELECT 105, 'sub2/', 103) AS A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources