How to find the file path of a flat file using a stored procedure (T-SQL)?

Question

I have a query. The problem is that I want to create a log file using T-SQL Stored Procedure. I need to get the Source File Path into the log file but I need to it dynamically. I don't want to hard code the path there.

How can I achieve this?

Answer 1

Do you mean you need to parse out the path and file from the full path/file name?

SET NOCOUNT ON

DECLARE @str_DIR_FILE VARCHAR(500) -- starting directory path and file name

DECLARE @str_DIR VARCHAR(400) -- directory path

DECLARE @str_FILE VARCHAR(100) -- file name


SET @str_DIR_FILE = 'c:\this\is\a\directory\path\to\a\file.txt'


-- get folder name

SELECT @str_DIR = REVERSE(RIGHT(REVERSE(@str_DIR_FILE),(LEN(@str_DIR_FILE)-CHARINDEX('\', REVERSE(@str_DIR_FILE),1))+1))

-- get file name

SELECT @str_FILE = REVERSE(LEFT(REVERSE(@str_DIR_FILE),CHARINDEX('\', REVERSE(@str_DIR_FILE),1)-1))

SELECT @str_DIR -- folder name

SELECT @str_FILE -- file name