SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE   PROCEDURE [dbo].[p_sys_fs_IsFile]
    @FileName nvarchar(4000),
    @Exists bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SET @Exists = 0;

    DECLARE @Sql nvarchar(max);

    CREATE TABLE #FileContent
    (
        Content nvarchar(max)
    );

    -- First try SINGLE_CLOB
    BEGIN TRY
        SET @Sql = N'
            SELECT BulkColumn
            FROM OPENROWSET(
                BULK ''' + REPLACE(@FileName, '''', '''''') + N''',
                SINGLE_CLOB
            ) AS F;';

        INSERT INTO #FileContent (Content)
        EXEC sys.sp_executesql @Sql;

        SET @Exists = 1;
        RETURN;
    END TRY
    BEGIN CATCH
        -- If this fails, try SINGLE_NCLOB
    END CATCH;

    -- Then try SINGLE_NCLOB
    BEGIN TRY
        SET @Sql = N'
            SELECT BulkColumn
            FROM OPENROWSET(
                BULK ''' + REPLACE(@FileName, '''', '''''') + N''',
                SINGLE_NCLOB
            ) AS F;';

        INSERT INTO #FileContent (Content)
        EXEC sys.sp_executesql @Sql;

        SET @Exists = 1;
    END TRY
    BEGIN CATCH
        SET @Exists = 0;
    END CATCH;
END;
GO
