sproc While Exist loop through recordset 1

[03Explorer]

I am versed on how to use Recordset in VBA (MS Access) and am attempting to mimic that code in SQL Server in a Sproc. I am somewhat new to sproc programming and was hoping to use the following code to use the row data of currently filtered values so I can use that data for filtering an inner loop.

	@GenerateMe is a Table Variable
	     aID INT,
	     PracticeID INT,
	     ClientID INT,
	     ChildRoleID INT,
	     ChildID VARCHAR(9),
	     ProjectName VARCHAR(255),
	     ParentID VARCHAR(9),
	     ParentRoleID INT,
	     ChildName VARCHAR(100), 
	     UniqueID VARCHAR(100)


WHILE EXISTS (SELECT * FROM @GenerateMe)
	BEGIN
		Print "Practice: " + PracticeID
		Print "ClientID: " + ClientID
		Print "ProjectName: " + ProjectName
		Print "ChildID: " + ChildID
	END

My goal is after ChildID is to have an inner Loop that uses the row of data that is filtered from @GenerateMe as a filter for an inner loop.

Summary of code I used in VBA

    Dim db As DAO.Database
    Dim rsE3Grouping As DAO.Recordset   
    SQL = 'SELECT aID, ParentID, PracticeID FROM table'
    Set rsE3Grouping = CurrentDb.OpenRecordset(SQL, dbOpenDynaset, dbFailOnError + dbSeeChanges)
    Do Until rsE2Grouping.EOF = TRUE
        debug.print rsE2Grouping!aID
        debug.print rsE2Grouping!ParentID
        debug.print rsE2Grouping!PracticeID
    rsE2Grouping.MoveNext
    Loop '--rsE2Grouping
rsE2Grouping.Close
Set rsE2Grouping = Nothing

 

[bborissov]

DECLARE @Id int
DECLARE @PracticeID int,
        @ClientID int,
        @ProjectName VARCHAR(255),
        @ChildID  VARCHAR(9)


SELECT @Id = MIN(aId) FROM @GenerateMe 
WHILE @Id IS NOT NULL
BEGIN
   SELECT @PracticeID = PracticeID
         ,@ClientID   = ClientID
         ,@ProjectName= ProjectName
         ,@ChildID    = ChildID
   FROM @GenerateMe
   WHERE aId = @Id
   Print "Practice: " + CAST(@PracticeID as varchar(10))
   Print "ClientID: " + CAST(ClientID as varchar(10))
   Print "ProjectName: " + ProjectName
   Print "ChildID: " + ChildID
   SELECT @Id = MIN(aId) FROM @GenerateMe  WHERE aId > @Id
END

NOT TESTED

You could use CURSOR if you like, just google "TSQL CURSOR"

Borislav Borissov
VFP9 SP2, SQL Server

 

[03Explorer]

Wow, thank you bborissov!

I like your method of incrementing the aID!