DECLARE @Path varchar(50), @FileName varchar(20)
SET @Path = 'C:\myfiles\'
Declare @files table(filenames varchar(255),depth int,isfile bit)
insert into @files
EXEC xp_dirtree  @path, 10, 1
Declare cur Cursor for
Select top 10 filenames from @files where isfile=1
open cur
fetch next from cur into @filename
while @@FETCH_STATUS=0
              BEGIN
                   
                            print @path + @filename

                     BEGIN TRY
                            print 'OK: ' + @FileName

                     END TRY

                     BEGIN CATCH

                            print ' '

                            print 'Error: ' + @FileName

                      END CATCH
fetch next from cur into @filename
              END

			  close cur
			  deallocate cur

• Edited by pIdrA 7 hours 24 minutes ago

DECLARE @Path varchar(50), @FileName varchar(20)
SET @Path = 'C:\myfiles\'
Declare @files table(filenames varchar(255),depth int,isfile bit)
insert into @files
EXEC xp_dirtree  @path, 10, 1
Declare cur Cursor for
Select top 10 filenames from @files where isfile=1
open cur
fetch next from cur into @filename
while @@FETCH_STATUS=0
              BEGIN
                   
                            print @path + @filename

                     BEGIN TRY
                            print 'OK: ' + @FileName

                     END TRY

                     BEGIN CATCH

                            print ' '

                            print 'Error: ' + @FileName

                      END CATCH
fetch next from cur into @filename
              END

			  close cur
			  deallocate cur

• Edited by Idr_p Thursday, August 27, 2015 11:43 PM
• Marked as answer by bassmann2 6 hours 19 minutes ago

DECLARE @Path varchar(50), @FileName varchar(20)
SET @Path = 'C:\myfiles\'
Declare @files table(filenames varchar(255),depth int,isfile bit)
insert into @files
EXEC xp_dirtree  @path, 10, 1
Declare cur Cursor for
Select top 10 filenames from @files where isfile=1
open cur
fetch next from cur into @filename
while @@FETCH_STATUS=0
              BEGIN
                   
                            print @path + @filename

                     BEGIN TRY
                            print 'OK: ' + @FileName

                     END TRY

                     BEGIN CATCH

                            print ' '

                            print 'Error: ' + @FileName

                      END CATCH
fetch next from cur into @filename
              END

			  close cur
			  deallocate cur

• Edited by Idr_p Thursday, August 27, 2015 11:43 PM
• Marked as answer by bassmann2 Saturday, August 29, 2015 12:49 AM

Thanks all, I've marked both response as correct. I went with Idr_p's suggestion in the end. I haven't typically been in the habit of using temp tables as solutions but to be honest don't have a great understanding of this approach.

Off the top of my head, would I be correct that Idr_p's approach would work in memory and the temp options need to write to disk? If so, does working in memory have a performance boost?

Thanks,

Bassmann

Hi,

>> I've marked both response as correct

That is great, this is the right way in my opinion if a question get several right responses. Unlike a chat, a forum thread is here to stay, and future users (especially those that come from searching engine) might see the responses and choose a different solution. Therefore all currect answer should be marked.

1. With that was said, I think that your solution is not a one that I recommends for most cases. There is no reason to do any looping in this case. SQL Server is a Tabular database, which based on SET, and we should avoid looping through records and working with it record by record, as much as we can!

2. You do not have to use the temporary table in order to avoid looping. In both links that posted the job is done in two steps: firstly we get the information, and secondly we use the data from step 1. You can avoid using temporary table and work in one step, but using the approach in the second link that I posted: create one dynamic query using "for xml" hint, and execute it directly.

>> Off the top of my head, would I be correct that Idr_p's approach would work in memory

basically SQL Server always work in memory. Even when we read data (select) or write it (insert/update) to table, which is on disk, SQL Server will firstly read the data to the memory and work in memory. Once in a while, SQL Server do a CHECKPOINT operation which writes the current in-memory modified pages from memory to disk.

The big disadvantage (which is one of the advantage as well, depending on the case) of using temporary table is that the code execute in two steps, therefore the SQL Server Query engine built an execution plan for each step separately which mean that this plan is optimized for the specific step and not for the entire process that we want to do. When you use inline code the SQL Server query engine can parse the entire text and build a combine execution plan which is optinize for the whole process. In order to chose what fit your need you need to examine it case by case.

** For more information regarding "Temp table", "Table-Valued Parameters", and "Table Variables" check this link:
https://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/

** I recommend to search Google for: SQL Server temp table vs inlin