How To Dynamically Pull Stored Procedure Arguments

I'm sure there is a way using the system tables or maybe on the stored
procedures of the master db to pull the arguments for a given stored
procedure, and their datatypes.
So, for a stored proc like this:
CREATE StoredProc1
OrderNumber AS BIGINT,
OrderName AS VARCHAR(50)
AS ...
I would, using this magical query I am hoping exists, get back:
OrderNumber, BIGINT, 4
OrderName, VARCHAR, 50
or something in that order. Anyone have any ideas?Try,
use northwind
go
exec sp_procedure_params_rowset 'SalesByCategory'
go
AMB
"David Samson" wrote:

> I'm sure there is a way using the system tables or maybe on the stored
> procedures of the master db to pull the arguments for a given stored
> procedure, and their datatypes.
> So, for a stored proc like this:
> CREATE StoredProc1
> OrderNumber AS BIGINT,
> OrderName AS VARCHAR(50)
> AS ...
> I would, using this magical query I am hoping exists, get back:
> OrderNumber, BIGINT, 4
> OrderName, VARCHAR, 50
> or something in that order. Anyone have any ideas?|||From MS SQL Books On Line, you'll read about querying INFORMATION_SCHEMA.PAR
AMETERS:
"Contains one row for each parameter of a user-defined function or stored pr
ocedure accessible to the current user in the current
database. For functions, this view also returns one row with return value in
formation.
The INFORMATION_SCHEMA.PARAMETERS view is based on the sysobjects and syscol
umns system tables.
To retrieve information from these views, specify the fully qualified name o
f INFORMATION_SCHEMA view_name."
"David Samson" <CaptainSlock@.nospam.nospam> wrote in message news:54296B5C-F33F-426A-95A3-7
7366FF14200@.microsoft.com...
> I'm sure there is a way using the system tables or maybe on the stored
> procedures of the master db to pull the arguments for a given stored
> procedure, and their datatypes.
> So, for a stored proc like this:
> CREATE StoredProc1
> OrderNumber AS BIGINT,
> OrderName AS VARCHAR(50)
> AS ...
> I would, using this magical query I am hoping exists, get back:
> OrderNumber, BIGINT, 4
> OrderName, VARCHAR, 50
> or something in that order. Anyone have any ideas?|||David Samson (CaptainSlock@.nospam.nospam) writes:
> I'm sure there is a way using the system tables or maybe on the stored
> procedures of the master db to pull the arguments for a given stored
> procedure, and their datatypes.
> So, for a stored proc like this:
> CREATE StoredProc1
> OrderNumber AS BIGINT,
> OrderName AS VARCHAR(50)
> AS ...
> I would, using this magical query I am hoping exists, get back:
> OrderNumber, BIGINT, 4
> OrderName, VARCHAR, 50
> or something in that order. Anyone have any ideas?
SELECT c.name, t.name, c.length, c.precision, c.scale
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
WHERE o.name = @.name
ORDER BY c.colid
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi There,
Why not simply try
sp_help 'procedure name'
OR
Select the name of Procedure and press (Alt+F1) If your QA is
customized for that
With warm regards
Jatinder Singh

How to drop merge replication system tables

Hi, we have a database wich used merge replication.
We disabled merge on this db, but '%onflict%' system tables persists,
leading to error when trying to drop them.
How can I drop those tables?
TIA,
Roberto Souza.
You could use sp_subscription_cleanup
There's a fairly good list of sp's used in replication at this site:
http://doc.ddart.net/mssql/sql70/sp_00.htm
"Roberto Souza" wrote:

> Hi, we have a database wich used merge replication.
> We disabled merge on this db, but '%onflict%' system tables persists,
> leading to error when trying to drop them.
> How can I drop those tables?
> TIA,
> Roberto Souza.
>
>
|||AFAIR this won't remove them. You should be able to use
drop table and the tablename from Query Analyser though.
Rgds,
Paul Ibison

How to do this in SQL Server Stored Procedure

Here is my problem:

I am designing Support System. I have a stored procedure for storing new Support Ticket. This stored procedure internally gets next ticket number and inserts Support Ticket

CREATE PROCEDURE [sp_SaveSupportTicket]
(
@.pid int,
@.uidGen int,
@.status VarChar (100),
@.probDes text,
@.probSol text,
@.guestName VarChar (100),
@.os VarChar (100),
@.roomNum VarChar (100)
)
AS
DECLARE @.ticNum int
SELECT @.ticNum = MAX(ticNum) + 1 FROM sup_TicDetails
INSERT INTO sup_TicDetails ( ticNum, pid, uidGen, status, probDes, probSol, guestName, os, roomNum,dateofsub)
VALUES (@.ticNum, @.pid, @.uidGen, @.status, @.probDes, @.probSol, @.guestName, @.os, @.roomNum, CONVERT(VARCHAR,GETDATE(),101))
GO

Now... before this happens, on my ASP.NET Page I have a label Ticket# . This label displays next ticket number

CREATE PROCEDURE [sp_GetNextTicketNumber] AS
SELECT max (ticNum) + 1
FROM sup_TicDetails
GO

Now.. how can I have only 1 stored Procedure so that I can obtain next ticket number and display it on ASP.NET page and when I hit "Submit Ticket" sp_SaveSupportTicket gets executed ??

I hope I have made my problem clear !! If not let me know......What you are doing will fail under load. BEtween the time you get the ticket number and insert, another thread could have inserted that same number. Why not use an IDENTITY column? Is it really essential to have the ticket number before you actually save it?|||I see what you are saying !!! no its not necessary to get ticket number before I save it.. it is just to display purpose.

There are problems with identity column ( I read in couple of aticles) so, I am not using Identity column..

Ok, if I don't display ticket number before saving it on my ASP.NET page.. will it still fail while load ??

How to handle thread part !!!!|||I have not heard of the problems you describe with Identity columns. They are certainly more reliable than what you have done here. Even if you do not display the number first, what you are doing will be prone to failure if multiple users are hitting the stored procedure at once. Trust me, Identity is the answer, and then use Scope_Identity() to get the new ID created right after an insert.|||Read this...

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

Note that if you delete row/s in a table and start adding row/s, the identity column will pick up where it left off.

If I delete a ticket, or try to rollback, I will in problem with Identity column...|||Here is link on MS Website describing Identity Column problem

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_3iex.asp

In our case, I am not sure, but we might have to delete some tickets and than I will be in trouble when I run Reporting Query !!!!!|||After talking to you I me and my friend tried ...

We both saw Ticket# 617 on our ASP.NET display page...

After entering data and hitting "submit", we went into Database to see what happend.

We saw 2 tickets Ticket# 617 created by me and 618 created by him.

So I guess concurrent access didn't gave us problem for insertion.. it displays wrong ticket number on screen ....

I guess I should remove Ticket# from screen than !!!!!!!!!!!

Any other idea ...|||The problem is that you may not be able to duplicate it at will, but the code you are using CAN fail if the two request come in at EXACTLY the same moment.|||Ok.. douglas

I gave you reason why I am not using Identity Column and I told you what happened when we submitted tickets.

I am understanding what you are saying.. .so what is the solution ? What are the chances that both tickets arriave to database at exactly same time ? What happens in that case ?

How can I handle this issue ? Everyone who is developing Web Applications might be running into same issues, how they are handling this or how are you handling such problem ?|||I use Identity columns as meaningless keys to link various rows in a database. It would not bother me if a row was deleted, because I would never rely on these numbers to know how many tickets were outstanding. I would use a SQL Query, not rely upon the ID number of the row. This matches what you would do in a system of paper tickets, because if a ticket is discarded, you would void the ticket and not reuse that number.

How often will folks have a problem with duplicate ticket numbers? Hard to say. You could use some sorts of locking inside of transactions. This is not something I would normally do, and it will serialize this section of code, meaning that only a single user at a time will be able to get to this section of code.|||hummm... Thats interesting !!!! If that is the case how will you set up primary key in your table ?

Here is structure of my table:

ticNum int 4 (primary key) (not auto generated, not identity)
pid int 4 (Property ID Referes to Property Table Primary Key)
uidGen int 4 (UserID Referes to User table Primary Key)
status varchar100
probDes text 16
probSol text 16
guestName varchar100
os varchar100
roomNum varchar100
dateofsub smalldatetime4|||Here is what I found out.. I have total 614 tickets, but when I do SELECT COUNT(*) from sup_TicDetails I see 614. I scanned my records and found that Tic# 100 and 472 are missing.

So I guess my previous developer might be using Identity. I removed Identity column after reding 2 articles i mentioned.

if I try convert primary key into Identity column now, will I be having any problem ?

How can I reset my Identity column every once in a while to fill out gaps or is it Ok to Have Column as identity & primary key and have gap in it ??|||It causes NO PROBLEM AT ALL to have gaps in the numbers.|||So, how you do testing of your code ?Everytime I Add/delete I am going to create gaps and when I transfer that on my production system, I am going to have gap tooo.....

How do I handle database on local system and on production system ?|||This is never something that has been an issue for me...

How to do multiple rows insert?

I want to do bulk insert of data into table.
I need to do it using System.Data.SqlServerCe namespace;

Already tryed 2 methods.

1)
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (val1, val2); Insert INTO [Table] (col1, col2) Values(val11, val22)";
if (Connection.State != System.Data.ConnectionState.Closed) {
Connection.Close();
}
Connection.Open();
command.ExecuteNonQuery();
Connection.Close();

Doesn't work because of parsing error. Appearantly semicolon isn't understood in commandText, although if commandText is executed in SQL Management Studio it executes flawlessly.

2)
SqlCeCommand command = Connection.CreateCommand();

command.CommandText

= "INSERT INTO [Table] (col1, col2) SELECT val1, val2 UNION ALL SELECT val11, val12";

if (Connection.State != System.Data.ConnectionState.Closed) {
Connection.Close();
}
Connection.Open();

command.ExecuteNonQuery();

Connection.Close();

Using this method i found out bug (or so i think).

I need to insert around 10000 rows of data and wouldn't want to run
Connection.Open();
Command.Execute();
Connection.Close();
cycle for 10000 times.

Any help would be appreciated. Thnx in advance.

P.S.
Sorry for bad english.

Hi,
why open, execute and close?

Why not open at the beginning, do all your executes, and only close when you exit the program?

That should be much quicker

Pete

|||U r absolutly right. Not opening and closing connection every time reduced incertion time from 10min. to ~4min.

But even if opening and closing connection only once data incertion last for ~4 mins. and that is unappropriate.

If I could cut data incertion to ~2 mins than it would be OK.|||

Try using parameterized queries for bulk inserts.

// Arranging Data in an Array.

const int no_of_values = 2;
int[] val1 = new int[no_of_values];
int[] val2 = new int[no_of_values];
val1[0] = val1;
val2[0] = val2;
val1[1] = val11;
val2[1] = val22;

// Do the inserts using Parameterized queries.

Connection.Open();
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (@.val1, @.val2)";

for (int i = 0; i < no_of_values; i++)
{
command.Parameters.Clear();
command.Parameters.Add("@.val1", val1[ i ]);
command.Parameters.Add("@.val2", val2[ i ]);
command.ExecuteNonQuery();
}

Connection.Close();

|||Here is a piece of VB code that does what you want - takes 2 seconds on a slowish PC. This uses a prepared parameterised INSERT statement, with the values of the parameters changed for each insert

Dim cn As SqlCeConnection
Dim cmd As New SqlCeCommand
Dim loopCount As Integer

cn = New SqlCeConnection()
cn.ConnectionString = "Data Source = |DataDirectory|\test.sdf"
cmd.Connection = cn

Try
cn.Open()
cmd.CommandText = "INSERT INTO TestTable (col1, col2) VALUES (@.col1, @.col2)"
cmd.Parameters.Add("@.col1", SqlDbType.Int)
cmd.Parameters.Add("@.col2", SqlDbType.NVarChar, 100)
cmd.Prepare()

For loopCount = 1 To 10000
cmd.Parameters("@.col1").Value = loopCount
cmd.Parameters("@.col2").Value = "abc"
cmd.ExecuteNonQuery()
Next loopCount

Catch ex As Exception
Debug.Print(ex.ToString)
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
|||Since version 3.0, Microsoft provided the SqlCeResultSet class that allows for direct table insertions - bypassing the SQL query processor altogether. And this means *very fast* insertions.|||Here is the code using a recordset. In back to back tests with the 'INSERT' method, there was no significant difference for 10,000 records - both very fast. I guess at the end of the day it is down to personal preference.

Dim cn As SqlCeConnection
Dim cmd As New SqlCeCommand

Dim loopCount As Integer

cn = New SqlCeConnection()
cn.ConnectionString = "Data Source = |DataDirectory|\test.sdf"
cmd.Connection = cn

Try
cn.Open()
cmd.CommandText = "SELECT * FROM TestTable"

Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()

Debug.Print(Date.Now)

For loopCount = 1 To 10000

rec.SetInt32(0, loopCount)
rec.SetString(1, "Sample text")
rs.Insert(rec)

Next loopCount

Catch ex As Exception
Debug.Print(ex.ToString)
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try

Debug.Print(Date.Now)

End Sub|||Thank u very much.
Using SqlCeResultSet time of bulk inserting droped from ~4min. to 35s. (240s -> 35s)
That is wonderfull.

My solution in the end.

connection.Open();
while ((buffer = reader.ReadLine()) != null) {
if (buffer.Length == 0) { continue; } //skip empty lines

strArray = buffer.Trim().Split(separator);

if (strArray[0] == "HEADER:") {
// remove any previous data
command = connection.CreateCommand();
command.CommandText = "DELETE " + strArray[1];
command.ExecuteNonQuery();

strBuilder = new StringBuilder();
strBuilder.Append("SELECT " + strArray[2]);
for (int i = 4;i < strArray.Length;i += 2) {
strBuilder.Append(", " + strArray[ i ]);
}

strBuilder.Append(" FROM " + strArray[1]);
command.CommandText = strBuilder.ToString();
resultSet = command.ExecuteResultSet(ResultSetOptions.Updatable);
}
else {
//tables data rows
resultRecord = resultSet.CreateRecord();
resultRecord.SetValues(strArray);
resultSet.Insert(resultRecord);
}
}
connection.Close();

Allso want to mention that i was afraid that such insert can intermix data, but it worked like a charm.

By intermix i mean:
CREATE TABLE SOME_TABLE (
someCol1 [int],
someCol2 [nvarchar](50)
)

And in data file values are writen:

someCol2Val1, SomeCol1Val1
someCol2Val2, SomeCol1Val2 ....

But if Select query is writen indicating cols names, then insert is using the same order of cols as in Select query.

P.S.
Again thank you very much.


|||I was surpised that it took 35 seconds - I believe that if you moved " resultRecord = resultSet.CreateRecord();" into the "HEADER" block it would run faster still as you only need to create the record once rather than 10,000 times.
|||It is not often that I find a straightforward no-nonsense sample that I do not need to battle to put to use... nice job! I wish 90% of the info in cyberspace was like that(instead of 10%)
kudos to Mohit Khullar

How to do multiple rows insert?

I want to do bulk insert of data into table.
I need to do it using System.Data.SqlServerCe namespace;

Already tryed 2 methods.

1)
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (val1, val2); Insert INTO [Table] (col1, col2) Values(val11, val22)";
if (Connection.State != System.Data.ConnectionState.Closed) {
Connection.Close();
}
Connection.Open();
command.ExecuteNonQuery();
Connection.Close();

Doesn't work because of parsing error. Appearantly semicolon isn't understood in commandText, although if commandText is executed in SQL Management Studio it executes flawlessly.

2)
SqlCeCommand command = Connection.CreateCommand();

command.CommandText

= "INSERT INTO [Table] (col1, col2) SELECT val1, val2 UNION ALL SELECT val11, val12";

if (Connection.State != System.Data.ConnectionState.Closed) {
Connection.Close();
}
Connection.Open();

command.ExecuteNonQuery();

Connection.Close();

Using this method i found out bug (or so i think).

I need to insert around 10000 rows of data and wouldn't want to run
Connection.Open();
Command.Execute();
Connection.Close();
cycle for 10000 times.

Any help would be appreciated. Thnx in advance.

P.S.
Sorry for bad english.

Hi,
why open, execute and close?

Why not open at the beginning, do all your executes, and only close when you exit the program?

That should be much quicker

Pete

|||U r absolutly right. Not opening and closing connection every time reduced incertion time from 10min. to ~4min.

But even if opening and closing connection only once data incertion last for ~4 mins. and that is unappropriate.

If I could cut data incertion to ~2 mins than it would be OK.|||

Try using parameterized queries for bulk inserts.

// Arranging Data in an Array.

const int no_of_values = 2;
int[] val1 = new int[no_of_values];
int[] val2 = new int[no_of_values];
val1[0] = val1;
val2[0] = val2;
val1[1] = val11;
val2[1] = val22;

// Do the inserts using Parameterized queries.

Connection.Open();
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (@.val1, @.val2)";

for (int i = 0; i < no_of_values; i++)
{
command.Parameters.Clear();
command.Parameters.Add("@.val1", val1[ i ]);
command.Parameters.Add("@.val2", val2[ i ]);
command.ExecuteNonQuery();
}

Connection.Close();

|||Here is a piece of VB code that does what you want - takes 2 seconds on a slowish PC. This uses a prepared parameterised INSERT statement, with the values of the parameters changed for each insert

Dim cn As SqlCeConnection
Dim cmd As New SqlCeCommand
Dim loopCount As Integer

cn = New SqlCeConnection()
cn.ConnectionString = "Data Source = |DataDirectory|\test.sdf"
cmd.Connection = cn

Try
cn.Open()
cmd.CommandText = "INSERT INTO TestTable (col1, col2) VALUES (@.col1, @.col2)"
cmd.Parameters.Add("@.col1", SqlDbType.Int)
cmd.Parameters.Add("@.col2", SqlDbType.NVarChar, 100)
cmd.Prepare()

For loopCount = 1 To 10000
cmd.Parameters("@.col1").Value = loopCount
cmd.Parameters("@.col2").Value = "abc"
cmd.ExecuteNonQuery()
Next loopCount

Catch ex As Exception
Debug.Print(ex.ToString)
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
|||Since version 3.0, Microsoft provided the SqlCeResultSet class that allows for direct table insertions - bypassing the SQL query processor altogether. And this means *very fast* insertions.|||Here is the code using a recordset. In back to back tests with the 'INSERT' method, there was no significant difference for 10,000 records - both very fast. I guess at the end of the day it is down to personal preference.

Dim cn As SqlCeConnection
Dim cmd As New SqlCeCommand

Dim loopCount As Integer

cn = New SqlCeConnection()
cn.ConnectionString = "Data Source = |DataDirectory|\test.sdf"
cmd.Connection = cn

Try
cn.Open()
cmd.CommandText = "SELECT * FROM TestTable"

Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()

Debug.Print(Date.Now)

For loopCount = 1 To 10000

rec.SetInt32(0, loopCount)
rec.SetString(1, "Sample text")
rs.Insert(rec)

Next loopCount

Catch ex As Exception
Debug.Print(ex.ToString)
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try

Debug.Print(Date.Now)

End Sub|||Thank u very much.
Using SqlCeResultSet time of bulk inserting droped from ~4min. to 35s. (240s -> 35s)
That is wonderfull.

My solution in the end.

connection.Open();
while ((buffer = reader.ReadLine()) != null) {
if (buffer.Length == 0) { continue; } //skip empty lines

strArray = buffer.Trim().Split(separator);

if (strArray[0] == "HEADER:") {
// remove any previous data
command = connection.CreateCommand();
command.CommandText = "DELETE " + strArray[1];
command.ExecuteNonQuery();

strBuilder = new StringBuilder();
strBuilder.Append("SELECT " + strArray[2]);
for (int i = 4;i < strArray.Length;i += 2) {
strBuilder.Append(", " + strArray[ i ]);
}

strBuilder.Append(" FROM " + strArray[1]);
command.CommandText = strBuilder.ToString();
resultSet = command.ExecuteResultSet(ResultSetOptions.Updatable);
}
else {
//tables data rows
resultRecord = resultSet.CreateRecord();
resultRecord.SetValues(strArray);
resultSet.Insert(resultRecord);
}
}
connection.Close();

Allso want to mention that i was afraid that such insert can intermix data, but it worked like a charm.

By intermix i mean:
CREATE TABLE SOME_TABLE (
someCol1 [int],
someCol2 [nvarchar](50)
)

And in data file values are writen:

someCol2Val1, SomeCol1Val1
someCol2Val2, SomeCol1Val2 ....

But if Select query is writen indicating cols names, then insert is using the same order of cols as in Select query.

P.S.
Again thank you very much.


|||I was surpised that it took 35 seconds - I believe that if you moved " resultRecord = resultSet.CreateRecord();" into the "HEADER" block it would run faster still as you only need to create the record once rather than 10,000 times.
|||It is not often that I find a straightforward no-nonsense sample that I do not need to battle to put to use... nice job! I wish 90% of the info in cyberspace was like that(instead of 10%)
kudos to Mohit Khullar

How To do a recursive join

Guys,

I really need to know how to do a recursive join in SQL.

Imagine any recursive data-structure such as a file directory system.

I could have a ParentDirectory table defined as:

PKey, PName

1 , DirA

2 , DirB

3 , DirC

I could then have a ChildDirectory defined as:

FKey, CName

1, DirB

1, DirD

2, DirC

2, DirE

Given a specific directory from ParentDirectory such as DirA i want to know all its direct and indirect sub-directories, such that i get the following set:

DirA, DirB

DirA, DirC

DirA, DirE

DirA, DirD

As DirA includes DirB then DirB includes DirC and DirE so transitively they are sub-directories of DirA.

This is quite simple to do in a procedural language but its mind boggling to do in SQL for me. Can someone please help.

Note: Although im using directories for this example, the items in the child table could have multiple parents in the parent table.

Thanks

The first thing that I might suggest is to change your table structure to something more like this:

declare @.mockup table
( PKey integer,
parent integer,
dirName varchar(5)
)
insert into @.mockup
select 1, null, 'DirA' union all
select 2, 1, 'DirB' union all
select 3, 2, 'DirC' union all
select 4, 1, 'DirD' union all
select 5, 2, 'DirE'
select * from @.mockup

/*
PKey parent dirName
-- -- -
1 NULL DirA
2 1 DirB
3 2 DirC
4 1 DirD
5 2 DirE
*/

This structure provides a lot of flexibility in that not only can it handle the output from your request like this:

;with subDirectory as
( select PKey as rootKey,
PKey as currentKey,
dirName as rootDir,
dirName as currentDir
from @.mockup
where parent is null

union all
select a.rootKey,
b.PKey,
a.rootDir,
b.dirName
from subDirectory a
inner join @.mockup b
on a.currentKey = b.parent
)
select rootDir + ', ' + currentDir as [base/Subdirectory]
from subDirectory
where rootKey != currentKey

/*
base/Subdirectory
--
DirA, DirB
DirA, DirD
DirA, DirC
DirA, DirE
*/

This table structure allows for more.

|||

Another request that we have seen at this forum is to show the directory path. This can be done something like this:

declare @.mockup table
( PKey integer,
parent integer,
dirName varchar(5)
)
insert into @.mockup
select 1, null, 'DirA' union all
select 2, 1, 'DirB' union all
select 3, 2, 'DirC' union all
select 4, 1, 'DirD' union all
select 5, 2, 'DirE'
select * from @.mockup

;with subDirectory as
( select PKey,
cast(right(' ' + dirName,5) + ',' as varchar(60)) as pathBasis
from @.mockup
where parent is null
union all
select b.PKey,
cast(a.pathBasis + right(' ' + b.dirName,5) + ',' as varchar(60))
from subDirectory a
inner join @.mockup b
on a.Pkey = b.parent
)
select reverse(substring (reverse(replace (replace (pathBasis, ' ', ''), ',', ', ')), 3, 70))
as tree
from subDirectory
order by pathBasis

/*
tree
-
DirA
DirA, DirB
DirA, DirB, DirC
DirA, DirB, DirE
DirA, DirD
*/

|||

Thanks for your input Kent. Its going to take me a while to understand that, as it is far removed from my initial simplistic schema.

Plus SQL just does not come easy or naturally to me.

The problem is a given item in the child table can have multiple parents in the parent table.

For example an employee who reports to multiple bosses, or a subclass that multiple inherits.

Can you give an example with the original schema, or are you saying like that it is not possible?

I cannot easily change the table structure.

Again is it possible to recurse on having to first join two tables - Its this i cannot get my head round?

Thanks for your help.

|||

Yes, I can certainly put this together; however, I am afraid that I don't understand a metaphor. To me a sub-directory has a single parent. Moreover, I think that with the schema you have provided that it is possible to have circular mobius structures. Hang on, and I will put together your original request. Maybe somebody that already has it can post their version too.

I think the mobius structure issue is mute; I think either scheme can have mobius loops. Ignore that particular comment.

I have reached my end-of-day and I must leave ontime today; perhaps someone else can help you with this. What I can see from this that I don't like is that in some cases I seem to be joining based on the PKey and FKey but in other cases joining based on names -- and I think that is funky.

Hopefully someone else can complete this. Also, I would like other opinions on this joining by names.

Kent

|||

Thanks Kent, it possible it could have loops - or the transitive closure as i like to call it.

Where say A -> A could come out. But yes lets not worry about this at the moment.

And Yes the directories is not a great example, just an attempt to map it onto an everyday familar object.

And you are right the parent has its direct set of child relations via the joining of the PKey with the FKey.

But it just so happens that the name in the same child table rows can then be joined with the name in the parent table - and so on.

In effect this is the indirect recursion.

If i could just break through on this - id be eternally grateful...

Thanks.

|||

A very good short article on Recursive Common table Expressions (CTEs) which helped me understand them is:

http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp

This is the construct that Kent is using (and is only available in SQL Server 2005).

|||

Thanks that throws a bit more light on it.

But it seems to me to only support the case of a single parent, and consequently the table that references itself

simply by having something like the following two member columns

ID

ParentID

When a problem domain has multiple parents or dependencies then the table structure needs to change under the

rules of normalisation. Consequently we end up with something like the schema i have shown here.

ParentTable

ID

ChildTable

ID

ParentID

Where ParentID in ChildTable is a foreign key on ID in ParentTable.

ID and ParentID in ChildTable would form a primary composite key or a unique pair.

Is there any other way of representing it so that we could use recursive CTE ?

I noticed the restriction in the article that the recursive member query could only reference one CTE.

All the examples are only recursing over a single table implying a single parent hierarchy.

Are we saying its not possible currently to recurse over a structure with multiple parents using SQL ?

So many real world problems have this property.

I still believe it is possible but just cannot see it yet. Else if it is not possible then i can give up.

|||

"All the examples are only recursing over a single table implying a single parent hierarchy."

I'm not sure why you say this.

I suspect the recursive solution will work even if you have multiple records for a single child, e.g., to have "multiple managers/bosses" for the same employee.

I think the main thing is that you must specify a limit on the number of recursions for your recursion relationship, so that you will not be harmed terribly by loops within the data.

Your recursion limit would basically be slightly larger than the maximum depth you anticipate for your tree.

Your table would have columns like

P_Key,

Child_ID,

Parent_ID

Each child could have as many parents as you like (one record for each parent). Any root(s) of the tree would either have a NULL Parent_ID, or a bogus Parent_ID that never occurs in the Child_ID column.

Hope that helps,

Dan

|||

Thanks Dan.

So with my orginal schema can you provide an example of a recursive CTE that would work?

Kent, what happened to you?

I thought you were the one who was going to shine the light! :-)

|||

Sorry Dan, i think i see what you are suggesting now. Using a link table.

The problem is that the Child table is not the same entity as the parent table although some entries from the child table can join with the rows in the parent table others will not.

Imagine a file that depends on other files.

FileA

use FileB

use FileC

FileB

use FileX

the dependencies are references only and not actual files, unless a match by names is found in the parent table.

The parent table will have alot more properties(columns) that the child(references) table does not.

Its this aspect that allows it to become useful as i can recursively find all the unresolved references etc.

Sky is the limit - if i could just work out with all your help a working example.

Or at least understand why it cannot work this way.

Please help...

|||

PR,

Here is what I have in mind, based on the data you provided in your first post. It relies on a #temp table based on the design in my previous post. (You have my apologies for the spacing in the code below: I cut and paste from Notepad, as has been suggested for this forum, but the cut/paste operation seems to trash the spacing. The spacing is fine if I paste it into another text editor, Word, etc. -- anywhere but in this Forum. I don't know what I'm doing wrong in that regard.)

Dan

[code language = 'SQL']

-- Create a table to hold parent-child data for recursion.

create table #parent_child
(
child varchar(20),
parent varchar(20)
)

-- Populate the table from existing tables.

insert into #parent_child
(
child,
parent
)
select distinct
cname,
pname
from ParentDirectory pd
inner join ChildDirectory cd
on pd.PKey = cd.FKey

-- Index the table. Both indexes are useful if you intend to make CTEs that go "from parent down" and "from child up".

create index parent_child_ndx1
on #parent_child (child, parent)

create index parent_child_ndx2
on #parent_child (parent, child)

-- Create a CTE. (I'm not sure if you need separate "parent down" and "child up" situations, since this CTE seems to provide all the combinations for either approach, I think.)

with prnt_chld_cte (parent, child, dist_from_parent) AS
(
select
parent,
child,
1 as dist_from_parent
from #parent_child
union all
select
pcc.parent,
pc.child,
dist_from_parent + 1
from #parent_child pc
inner join prnt_chld_cte pcc
on pc.parent = pcc.child
)

-- Create a query that employs the CTE. Limit recursion to 20. (You can change that number to whatever you feel is appropriate to your data situation.)

select
parent,
child,
dist_from_parent
from prnt_chld_cte
order by 1, 3, 2
OPTION (MAXRECURSION 20);

[\code]

|||

Wow Dan, thanks for all that. It will take some time to work through all that but will give it a go and let you know the outcome.

I will need to pay you soon for working for me

Even though you are using directories as an example I assume it will work with multiple parents?

Yes i will want to traverse any way i can up down sideways inside out etc. But if i could just get one to work then I would begin to understand things alot better. Iv already ordered some books on SQL Server and T SQL as im obviously weak in this area.

Certainly i would want to say give me all the transitive dependents of x

And then all the trasitive dependencies of x.

Other questions would be give me all the dependencies of x that are not defined in the parent table etc etc etc.

Iv posted this on 3 of the major SQL Server forums now and still seeking a solution.

Im thinking of turning it into a competition where the person who provides a workable solution I will send a ￡50 cheque

What do you think?

|||

Dans the man.

Ok here is my version

Ok created a view of the following ParentChild

SELECT dbo.Table1.Name1 AS parent, dbo.Table2.Name2 AS child
FROM dbo.Table1 INNER JOIN
dbo.Table2 ON dbo.Table1.PKey = dbo.Table2.FKey

with prnt_chld_cte (parent, child, dist_from_parent) AS
(
select
parent,
child,
1 as dist_from_parent
from ParentChild
where parent = 'A''
union all
select
pcc.parent,
pc.child,
dist_from_parent + 1
from ParentChild pc
inner join prnt_chld_cte pcc
on pc.parent = pcc.child
)

select
parent,
child,
dist_from_parent
from prnt_chld_cte
order by 1, 3, 2
OPTION (MAXRECURSION 0);

And its working, at last thanks for everyone's help.

I have a new set of problems now, Loops, with the real dataset!

How to detect loops?

Thanks again.

|||

PR,

Maybe you can overcome the effects of LOOPS by putting a

WHERE NOT EXISTS (select * from prnt_child_cte pcc1 where pcc1.parent = pcc.parent and pcc1.child = pc.child)

into the "bottom" SELECT in the CTE.

I'm glad that this seems to be working for you.

I really would recommend against MAXRECURSION 0. Maybe 100, 1000, or something, but not 0.

Dan

P.S. I just tried this sort of thing with some data of my own, and found that you cannot refer to the "recursive member" in this fashion (WHERE NOT EXISTS).

Maybe you merely use a "select distinct parent, child" in a query using the CTE.