Function to return the binary value of an image

Question

I created the following Function to return the binary value of an image. However, when executing the query you are displaying the following message:

 CREATE FUNCTION [dbo].[fn_image](@DSCAMINHO VARCHAR(4000), @DSARQUIVO VARCHAR(4000)) 
    RETURNS VARBINARY (MAX)
    AS 
    BEGIN 

    DECLARE 
            @EXTENSAO VARCHAR(5) = '.PNG',
            @SQL NVARCHAR(4000),
            @BULKCOLUMN VARBINARY (MAX)

    SET @SQL = 'SELECT @BULKCOLUMN = BULKCOLUMN 
       FROM OPENROWSET(BULK ''' + @DSCAMINHO + @DSARQUIVO + @EXTENSAO + ''', SINGLE_BLOB'+') AS IMG'

    EXEC sp_executesql @SQL, N'@BULKCOLUMN VARBINARY (MAX) OUT', @BULKCOLUMN OUTPUT;

     RETURN @BULKCOLUMN

END

But when executing it

SELECT
    CAST (CDEMPRESA AS VARCHAR(20)) CDEMPRESA,
    CAST (CDDESTAQUE AS VARCHAR(20)) CDDESTAQUE,
    CAST (DSDESTAQUE AS VARCHAR(50)) DSDESTAQUE,
    dbo.FN_IMAGE(DIRDESTAQUE,DSDESTAQUE) AS  DIRDESTAQUE
FROM
    DESTAQUE 

I get the following error message:

Message 557, Level 16, State 2, Line 1 extended stored procedures can be performed on a function.

Answer 1

You cannot use Dynamic SQL or call a precedent from within a function.

https://stackoverflow.com/questions/15180561/getting-an-error-when-executing-a-dynamic-sql-within-a-function-sql-server