Stored procedure in SQL SERVER

See more:

SQL-Server

Hi all,

I have to write stored procedure which will file name,file name without extension from given path.I got following code from code project.The Problem is I have to get all files name in a given folder.The below code is working for given path. Can you please help me.

ALTER PROCEDURE [dbo].[ReadFileName]
    -- Add the parameters for the stored procedure here
    @FILE_PATH   VARCHAR(MAX),
    @NAME        VARCHAR(MAX) OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @FILE_LENGTH INT
    DECLARE @FILE_NAME VARCHAR(MAX)

    SET @FILE_LENGTH = LEN(@FILE_PATH)
    WHILE (@FILE_LENGTH <> 0)
    BEGIN
        IF SUBSTRING(@FILE_PATH, @FILE_LENGTH, 1) = '\' 
        BEGIN
            SET @FILE_NAME = RIGHT(@FILE_PATH, LEN(@FILE_PATH) - @FILE_LENGTH)
            BREAK
        END
        SET @FILE_LENGTH = @FILE_LENGTH - 1
    END
   
    SET @NAME = @FILE_NAME
    INSERT INTO TABLE_NAME(COL1,COL2,COL3) values(@FILE_NAME,@FILE_PATH,@FILE_NAME)
    UPDATE TABLE_NAME
        SET COL1 = REVERSE(SUBSTRING(REVERSE(COL1), 
        CHARINDEX('.', REVERSE(COL1)) + 1, 999))

    RETURN 
END