usp_DirFolder Stored Procedure

by Bill Dowling 28. July 2010 06:58
Returns the filenames contained in the passed Folder name. USE [DB_Name] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[usp_DirFolder] @PCWrite varchar(2000), -- The first parameter is a path to a directory @DBTable varchar(100)= NULL, -- The second parameter is a table name in which to insert the file names @PCIntra varchar(100)= NULL, -- The third parameter is a filter for including certain names @PCExtra varchar(100)= NULL -- The fourth parameter is a filter for excluding certain names AS SET NOCOUNT ON DECLARE @Return int, @Retain int, @Status int SET @Status = 0 DECLARE @Task varchar(2000), @Work varchar(2000), @Wish varchar(2000) /* ##### Example ##### Create Table #TempDir (FileName varchar(200)) EXECUTE spDirFolder '\\Webserver\logfiles\W3SVC1', #TempDir Select * From #TempDir Order By FileName Drop Table #TempDir */ SET @Work = 'DIR ' + '"' + @PCWrite + '"' CREATE TABLE #DBAZ (Name varchar(400), Work int IDENTITY(1,1)) INSERT #DBAZ EXECUTE @Return = master.dbo.xp_cmdshell @Work SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain IF @Status = 0 SET @Status = @Return IF (SELECT COUNT(*) FROM #DBAZ) < 4 BEGIN SELECT @Wish = Name FROM #DBAZ WHERE Work = 1 IF @Wish IS NULL BEGIN RAISERROR ('General error [%d]',16,1,@Status) END ELSE BEGIN RAISERROR (@Wish,16,1) END END ELSE BEGIN DELETE #DBAZ WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING(Name,40,1) = '.' OR Name LIKE '%.lnk' IF @DBTable IS NULL BEGIN SELECT SUBSTRING(Name,40,100) AS FileName FROM #DBAZ WHERE 0 = 0 And (Name NOT LIKE '%<DIR>%') AND (@PCIntra IS NULL OR SUBSTRING(Name,40,100) LIKE @PCIntra) AND (@PCExtra IS NULL OR SUBSTRING(Name,40,100) NOT LIKE @PCExtra) ORDER BY 1 END ELSE BEGIN SET @Task = ' INSERT ' + REPLACE(@DBTable,CHAR(32),CHAR(95)) -- 32 (space) 95 (_) + ' SELECT SUBSTRING(Name,40,100) AS FileName' + ' FROM #DBAZ' + ' WHERE 0 = 0' + ' And (Name NOT LIKE ''%<DIR>%'')' + CASE WHEN @PCIntra IS NULL THEN '' ELSE ' AND SUBSTRING (Name,40,100) LIKE ' + CHAR(39) + @PCIntra + CHAR(39) END + CASE WHEN @PCExtra IS NULL THEN '' ELSE ' AND SUBSTRING (Name,40,100) NOT LIKE ' + CHAR(39) + @PCExtra + CHAR(39) END + ' ORDER BY 1' IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR IF @Status = 0 SET @Status = @Return END END DROP TABLE #DBAZ SET NOCOUNT OFF RETURN (@Status)

Tags:

SQL Coding Examples

About the author

Bill Dowling has been a VB and .Net programmer for as long as he can remember.

Month List