The MLID in the header table will be generated automatically.All the Parents will be stored in the HEADER and their childs in the DETAIL.When a child is added to a parent,the Parent's MLID will be stored in the MLID field in the DETAIL table with the newly added child.That child will come to the PARENT table when a child is added to that.The MLITEM id in the parent table can be repeated when that item undergoes a rivision.But the MLID for this will be a new one.An item in the Parent Table can have any number of childs and these childs can have any number of children(there is no limit for the level.)
Now if i select an item id '1000' (for example from the Header Table) with a concatstring (it could be without a concat string also).all its childs and their children should be printed in a report like the following
************************************************** ***************************
I NEED TO CREATE THE TREE USING BOTH THE HEADER(MSTHDRML) AND THE DETAIL TABLE(MSTDTLML)
************************************************** ***************************
How can this be done.Is it necessary to use a recursive function in a stored procedure to generate this ...... i have never used Recursive function in SQL SERVER Stored Procedures.Can anyone help me on this(with Code).if not stored procedure, then what else can be done for this.
I am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod
Hi, I would like some help on a stored recursive procedure I have been working on for my assigment. I had the solution from my course master some time back, but accidentally deleted the file before finishing the course, and would like the solution to that problem. The questions are as follows: 1) Create a recursive stored procedure that counts from 1 to 10 2) Create a recursive stored procedure that counts from 10 to 1
I believe there's more than one approach in solving these procedures, and any feeback is welcome.
How do I call my Stored Procedure recursively:CREATE PROCEDURE dbo.GetParentIONode(@IONodeID int,@FullNodeAddress char(100) OUTPUT)ASBEGINDECLARE @ParentIONodeID intIF EXISTS (SELECT ParentIONodeID FROM IONodes WHERE IONodeID = @IONodeID)BEGINSET @FullNodeAddress = CAST((SELECT ParentIONodeID FROM IONodes WHEREIONodeID = @IONodeID) AS VARCHAR) + ' / ' + @FullNodeAddress--CALL SP Again with @ParentIONodeID and @FullNodeAddress untilParentIONodeID = NULLSELECT @FullNodeAddressENDENDGO
I need to develope a stored procedure (eventually called by a trigger) that creates a record in an event table for all the descendants of a drawing. There are 3 tables involved as example tables shown below:
DwgTable
DwgID (integer)--drawing record identifier
PrntDwgID (integer)--parent drawing record identifier (a previously defined DwgID from this table)
DwgEventTable
DwgEventID (integer)--record identifier
DwgID (integer)--value from DwgTable
EventID (integer)--value from EventTable
EventTable
EventID There are other fields in two of the tables and only the fields shown in the DwgEventTable, but only the fields shown are required for adding a record in the DwgEventTable for a new event in the EventTable. The problem is identifying all the DwgID's of the descendant of the DwgID where the EventID occurred. There may be 0 to N descendants in 0 to N generations. I need to add a record for the original DwgID and all the descendant DwgID's in the DwgEventTable for the event identified by EventID.
I could do this from the client side, but a better place would be from the server side. I need some clue(s) on how to start coding a recursive stored procedure in SQL Server 2005. From what I have read, you cannot create a managed code procedure that appends or updates records--if managed code can add/modify records then I can do the above with managed code procedure.
I apologize if I posted in the wrong section, but I cannot find a solution to this. I was hoping that some one can help me figure this out. I need this solution in a stored procedure VS using dataset because there are thousands of categories and it is extremely slow with a dataset. I have a category table ID intPARENTID intNAME SAMPLE DATA:
ID NAME PARENT_ID
1 ANIMALS 0
2 DOGS 1
3 CATS 1
4 Abyssinian 3
5 Persian 3
6 Rurkish Van 3
7 Dalmation 2
8 German Shepherd 2
9 Irish Setter 2
10 Bulldog 2
I need the stored proc to return results in a single record set like this: ANIMALS - DOGS - - Dalmation - - German Shepherd - - Irish Setter - - Bulldog - CATS - - Abyssinian - - Persian - - Turkish Van Seems fairly easy but I have been battling with this for days now. Thank you in advance, EL
Hello, I am hoping someone can help me in this. I am looking to write a stored procedure that will return the heirarchy of an organization. I will display how the heirarchy might look and then list the tables involved.
John Smith
- Jacob Jones - Lisa Thompson - Samuel Barber
- Paul Smith - John Jackson
Ok, so Jacob, Lisa, an Samuel report up to John Smith. Paul and John Jackson report up to Samuel Barber.
Here are the tables:
Users holds the user_id, first_name, last_name, and reports_to_user_id. User_Roles holds the user_id, role_type_id Role_Types holds the role_type_id, and the type (which could be Administrator, Standard, Guest) for example. In addition, Role_Types also has ranking which must be taken into consideration as well. 1 being the top rank and 9 being the lowest.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
I've got an application where I need to traverse a "tree" of parent and child relationships. I've got code in my application that does this nicely, and I want to move it to the database in the form of stored procs for a little better performance. The initial way I created it, is that the stored proc creates a cursor for the parent nodes, then calls itself for each child node, and rips through it that way. The problem is is that I get a cursor already exists error. Am I doing something wrong, or is that a limitation of SQL server to only have one named cursor open at a time? Is there a better way to do this?
I'm trying to write a recursive stored proc for the TreeView control that will return XML for the TreeView. Does anyone have any examples of code to do this or any resources that might help me write the stored proc?
Hi allI am implementing a stored procedure which needs to recursively callitself until specific condition is reached, Could anyone give someadvice about that?Thanks a lotRobert Song
On SQL 2012 (64bit) I have a CLR stored procedure that calls another, T-SQL stored procedure.
The CLR procedure passes a sizeable amount of data via a user defined table type resp.table values parameter. It passes about 12,000 rows with 3 columns each.
For some reason the call of the procedure is verz very slow. I mean just the call, not the procedure.
I changed the procdure to do nothing (return 1 in first line).
So with all parameters set from
command.ExecuteNonQuery()to create proc usp_Proc1 @myTable myTable read only begin return 1 end
it takes 8 seconds.I measured all other steps (creating the data table in CLR, creating the SQL Param, adding it to the command, executing the stored procedure) and all of them work fine and very fast.
When I trace the procedure call in SQL Profiler I get a line like this for each line of the data table (12,000)
SP:StmtCompleted -- Encrypted Text.
As I said, not the procedure or the creation of the data table takes so long, really only the passing of the data table to the procedure.
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
Hello to all, I have a stored procedure. If i give this command exce ShortestPath 3418, '4125', 5 in a script and excute it. It takes more 30 seconds time to be excuted. but i excute it with the same parameters direct in Microsoft SQL Server Management Studio , It takes only under 1 second time I don't know why? Maybe can somebody help me? thanks in million best Regards Pinsha My Procedure Codes are here:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[ShortestPath] (@IDMember int, @IDOther varchar(1000),@Level int, @Path varchar(100) = null output ) AS BEGIN
if ( @Level = 1) begin select @Path = convert(varchar(100),IDMember) from wtcomValidRelationships where wtcomValidRelationships.[IDMember]= @IDMember and PATINDEX('%'+@IDOther+'%',(select RelationshipIDs from wtcomValidRelationships where IDMember = @IDMember) ) > 0 end if (@Level = 2) begin select top 1 @Path = convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and PATINDEX('%'+@IDOther+'%',B.RelationshipIDs) > 0 end if (@Level = 3) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',C.RelationshipIDs) > 0 end if ( @Level = 4) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',D.RelationshipIDs) > 0 end if (@Level = 5) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D, wtcomValidRelationships as E where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0 and charindex(convert(varchar(100),E.IDMember),D.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',E.RelationshipIDs) > 0 end if (@Level = 6) begin select top 1 @Path = '' from wtcomValidRelationships end END
CREATE TABLE [dbo].[TransactionComponents]( [pkTransactionComponent] [int] IDENTITY(1,1) NOT NULL, [pkTransactionID] [int] NOT NULL, [ComponentID] [int] NULL ) ON [PRIMARY]
With the following data:-
INSERT [dbo].[TransactionComponents]([pkTransactionID], [ComponentID]) SELECT 1,5 UNION SELECT 1,6 UNION SELECT 1,7 UNION SELECT 1,8
[Code] ....
pkTransactionID and ComponentID both link to the same column on another table this enables a many to many relationship, what I need to figure out is a complete tree of relationships from one of the ID's in it. I think I need to write a recursive CTE to achieve this but I am not entirely sure how to write it. Below is my attempt:-
DECLARE @ID INT SET @ID = 1; WITH cteTxHeirachy (TxID, RelTxID, TxLevel)
[Code] ...
This returns:-
15 16 17 18 19 110
But the following are missing:-
10 2 2 11 2 12
3 and 4 should not be returned. I figured if I added the code that is commented out in the CTE that should give me everything but I think I get caught in an infinite loop.
Dear fellows, Can anybody tell me how can i apply recusive/Tree query using select statement. For example I've a table structure for an Organization as follows:
emp_id emp_name supervisor_id ---------- --------------- ------------------- 101 ZAFIAN 102 BRUNNER 101 108 CALLAHAN 102 105 RUSSO 102 110 SIM 102 103 DUELL 101 and so on
1. How can I get the above records in Hirarchical format starting from top or from anywhere else in the hierarchy?
In Oracle it can be done as follows: SELECT emp_id,emp_name,supervisor_id FROM employee_tbl CONNECT BY supervisor_id = PRIOR emp_id START WITH supervisor_id is null;
Please reply me at the following address if possible: faisal@visualsoft-inc.com
INSERT INTO Details SELECT '','7419-01',0 union all SELECT '7419-01','44342-00',1 union all SELECT '7419-01','45342-00',1 union all SELECT '7419-01','46342-00',1 union all SELECT '7419-01','47342-00',1 union all SELECT '7419-01','48342-00',1 union all SELECT '7419-01','49342-00',1 union all
[code]....
From the above table data i want a search query , for example if I search data with "52342-00" I want output to be below format using CTE.
I have searched the net for atmost two days to find the solution of this problem but we not able to get the solution. I would appritiate if any one could help me in solving this issue:
I want to write a trigger for deleting the EMPLOYEE with EMPID=1 and the trigger should delete all the employees as there is cascading among them i.e EMPID 1 is the Manager of EMPID=2 and so on..
I found a solution at: http://msdn2.microsoft.com/en-us/library/aa902684(sql.80).aspx but the solution does not work when i try to implement it . It deletes the record for abc,xyz in the above table but rest are not deleted by the trigger.
Suppose there's a table named [Category], which has 2 columns: CategoryID int, ParentCategoryID int
Each category, except the top most, has a parent category, it's a 1-to-n parent-children relationship. Now I want to write a stored proc./function that accepts a CategoryID input parameter, and output all the descendent CategoryIDs (son, grand son, ...). How to do that in MSSQL 2000?
I have created a package which has to move huge volume of data in chuncks from source to target with a few sorter transformations and many lookup transformation. Once a chunck is completed the Execute SQL Task in the package updates the etldates into a control table against the chunkid. I want this run in a loop, as on completion of a chunk another should start and follow the same process until there are no more chuncks in the control table. First i execute the package manually, then i have written a trigger to call the SQL Server Job (etl) on update of the dates for the chunkids in the control table. The job when being called by the on update trigger is throwing up the following error. "The job failed. The Job was invoked by User fwt obin. The last step to run was step 1 (Package1)". Am i missing something basic here?
PS: So i tried putting the package into a for each loop container, performance suffers. A chunck which used to take 2 hours is taking more than 12 hours to complete. Then i removed sorter transformation and used db sorting in my source OLE db. Then thought of cacheing the lookups but did not know how and what to give so i gave up. While iam here, DB sorting/SSIS sorting? which is better?
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Has anyone encountered cases in which a proc executed by DTS has the following behavior: 1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio 2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+) The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query) The Update ad-hoc query will finish in 2 minutes
I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
I would like to create a store procedures that will return me a full path by passing a PathID
I started with this code:
DECLARE @path_id int SET @path_id = 5; WITH fullPath (PathID, ParentPathID, Path) AS ( SELECT PathID, ParentPathID, Path FROM tblPath WHERE PathID = @path_id UNION ALL SELECT tblPath.PathID, tblPath.ParentPathID, tblPath.Path AS Path FROM tblPath JOIN fullPath ON tblPath.PathID = fullPath.ParentPathID ) SELECT * FROM fullPath
This code will return this: 1 NULL D: 2 1 Sections 3 2 Bin 4 3 Data 5 4 FinancialReport.doc
What I would like to get is something like this: D:/Sections/Bin/Data/FinancialReport.doc
Any help would be really appreciated. Lost too much time on it already. Thanks, pharvey
in few words i need subtotal only for who have children.I tried with rollup but i wasn't able to have it similar to the aspected.I put it the level just to let clearer the dependencies.
