>How do I get a list of subdirectories in a directory and then loop through them, changing a variable each time the subdirectory name changes? In Foxpro I can do it in about 4 minutes. Can't figure out how to do it with SQL Query.
>
>UPDATE: I found this...
>
>

>Execute master..xp_subdirs N'C:\ImpactMD\Images'
>

>
>...which is good, but how do I get them into something I can loop through?


Jay,

Using cursors in SQL is not very popular, but if you really have to use loop as programing tehnique, please find example below:

SET NOCOUNT ON

--Capture SP results into temp.table
CREATE TABLE #Temp1 (subdirectory NVARCHAR(1000))
INSERT INTO #Temp1 Execute master..xp_subdirs N'C:\TEMP'

--Create Cursor, so you can loop through it...
DECLARE @Subdir NVARCHAR(1000)
DECLARE crsTemp CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT * FROM #Temp1 
OPEN crsTemp

--here comes the loop
FETCH NEXT FROM crsTemp INTO @Subdir
WHILE (@@FETCH_STATUS = 0) BEGIN

	PRINT @Subdir

	FETCH NEXT FROM crsTemp INTO @Subdir
END	

--cleanup 
CLOSE crsTemp
DEALLOCATE crsTemp
if object_id('tempdb..#Temp1') is not null drop table #Temp1  

Cheers,

Zoran