Hello,Can I please get some assistance with the following stored procedure:I am trying to tweak this stored procedure in order to check against a date and list only the latest version of documents..I have surronded the section that does not work with *****Could someone please provide me with assistance, in that section I am trying to compare vercdate to the checkdate variable and I am trying to list only the latest version of each document, so only one version should be displayed not all versions..hope someone can helpThanks,AshaCREATE PROCEDURE dbo.sp_GetAllChildren (
@DataID int,
@CheckDate DATETIME) AS Declare
@Count int Declare
@SQL varChar(8000) set nocount on set
@Count = 1 CREATE TABLE #sp_GetAllChildren_Tmp ( ID int NOT NULL IDENTITY (1, 1), FolderLevel int NOT NULL, Name varChar(255), DataID int NOT NULL, ParentID int NOT NULL, ChildCount int, SubType int, DataSize int, Version int, VerCDate DATETIME NULL ) ON [PRIMARY] While
@Count < 50 Begin Set
@SQL = 'insert into #sp_GetAllChildren_Tmp select ' + cast(
@Count as varchar(50)) + ', Name, DataID, ParentID, childCount, subtype, IsNull(datasize,0), IsNull(version, 0),vercdate from dtree A with (nolock) left outer join dversdata B with (nolock) on A.dataID = B.DocID ******************************************WHERE docID + Versionid =(SELECT MAX(docID + dversdata.Versionid) FROM dversdata WHERE dtree.dataID=dversdata.docID) AND vercdate >' + CAST(
@CheckDate AS VARCHAR) ******************************************* if
@Count = 1 Set
@SQL =
@SQL + ' AND dataID = ' + Cast(
@DataID as varchar(50)) else Set
@SQL =
@SQL + ' AND ParentID in (select dataID from #sp_GetAllChildren_Tmp where FolderLevel = ' + cast(
@Count-1 as varchar(50)) + ')' exec (
@RowCount = 0 BREAK ELSE Set
@Count =
@Count + 1 end select * from #sp_GetAllChildren_TmpGO
