I am trying to use this code in Zabbix Monitoring (ODBC), but seems zabbix only can return the value when it is a select statement, otherwise it return "SQL query returned empty result".

BEGIN
    IF OBJECT_ID(N'tempdb..##temp_dbspace_solaire', N'U') IS NOT NULL DROP TABLE ##temp_dbspace_solaire
    Create table ##temp_dbspace_solaire(
    database_name varchar(100),
    dbspaceutilization decimal(15,2));
    
    INSERT INTO ##temp_dbspace_solaire(database_name,dbspaceutilization)
    exec sp_MSforeachdb'
    use [?]
    ;WITH CTE AS (
        SELECT
            CAST(fileproperty(a.name, ''spaceused'')/128.0 AS DECIMAL(15, 2)) AS spaceused,
            CASE
                WHEN max_size / 128.0 < 0 THEN 0
                ELSE CAST(max_size / 128.0 AS DECIMAL(15, 2))
            END AS maxsize,
            CASE
                WHEN max_size = -1 THEN 1
                ELSE 0
            END AS hasNegativeMaxSize
        FROM sys.database_files a
        LEFT JOIN sys.filegroups fg ON a.data_space_id = fg.data_space_id 
        WHERE a.type_desc = ''ROWS'' 
    )
    
    SELECT 
        DB_NAME() as database_name,
        CASE
            WHEN MAX(hasNegativeMaxSize) = 1 THEN 0
            ELSE SUM(spaceused) / NULLIF(SUM(maxsize), 0) * 100
        END AS dbspaceutilization
    FROM CTE;
    ';
    
    select COUNT(*) from ##temp_dbspace_solaire where dbspaceutilization > 85;
    END;

Is there any workaround to use select statement to return the result of the begin code?

something like it (of course, it does not work):

Select * from (

 BEGIN
    IF OBJECT_ID(N'tempdb..##temp_dbspace_solaire', N'U') IS NOT NULL DROP TABLE ##temp_dbspace_solaire
    Create table ##temp_dbspace_solaire(
    database_name varchar(100),
    dbspaceutilization decimal(15,2));

    INSERT INTO ##temp_dbspace_solaire(database_name,dbspaceutilization)
    exec sp_MSforeachdb'
    use [?]
    ;WITH CTE AS (
        SELECT
            CAST(fileproperty(a.name, ''spaceused'')/128.0 AS DECIMAL(15, 2)) AS spaceused,
            CASE
                WHEN max_size / 128.0 < 0 THEN 0
                ELSE CAST(max_size / 128.0 AS DECIMAL(15, 2))
            END AS maxsize,
            CASE
                WHEN max_size = -1 THEN 1
                ELSE 0
            END AS hasNegativeMaxSize
        FROM sys.database_files a
        LEFT JOIN sys.filegroups fg ON a.data_space_id = fg.data_space_id 
        WHERE a.type_desc = ''ROWS'' 
    )

    SELECT 
        DB_NAME() as database_name,
        CASE
            WHEN MAX(hasNegativeMaxSize) = 1 THEN 0
            ELSE SUM(spaceused) / NULLIF(SUM(maxsize), 0) * 100
        END AS dbspaceutilization
    FROM CTE;
    ';

    select COUNT(*) from ##temp_dbspace_solaire where dbspaceutilization > 85;
    END;
);

Edit: fileproperty function only return the space usage only for the current database, that is why I need to do all those stuffs inside sp_MSforeachdb . I need to return for all databases, not the current database. I am returning the usage space in percent based on the max size the database can reach. If the database has any mdf or ndf file with unlimited max size, it return 0%.