Execute the following Microsoft SQL Server T-SQL script to create a stored procedure for the parametrized execution of Command Prompt DIR command to obtain a directory listing of a folder.

WARNING: xp_cmdshell & SQL Server security

-- SQL Server create stored procedure with temporary table
-- T-SQL execute xp_cmdshell dynamically within a stored procedure
USE AdventureWorks2008;

GO

CREATE PROC uspDIR

           @Path VARCHAR(MAX)

AS

  BEGIN

    CREATE TABLE #DIR (

      ID   INT    IDENTITY ( 1 , 1 ),

      Line VARCHAR(512))

    DECLARE  @Command CHAR(2048),

             @Return  INT

    SET @Command = 'dir "' + @Path +'"'

    PRINT @Command -- test and debug

    INSERT #DIR

    EXECUTE @Return = master.dbo.xp_cmdshell  @Command

    DELETE #DIR WHERE Line is NULL

    SELECT *  FROM   #DIR

  END

GO

-- SQL stored procedure execution with one input parameter

EXEC uspDIR 'C:\Program Files\'

/*

Line

.....

02/06/2010  04:04 AM    <DIR>          Windows Desktop Search

09/10/2009  08:06 PM    <DIR>          Windows Media Connect 2

09/10/2009  08:28 PM    <DIR>          Windows Media Player

08/15/2009  12:16 PM    <DIR>          Windows NT

....

*/