Return result set from function with dynamic query

I am trying to return result set from function which use executes a command string with Execute statement.

create function GetLogApiCalls
(
@displayStart int,
@displayLength int,
@searchString nvarchar(1000),
@orderBy nvarchar(100),
@orderByDirection varchar(50)
)
returns @logs table(
[Id]                INT     NOT NULL,
[Provider]          NVARCHAR (10)    NULL,
[RequestIdentifier] UNIQUEIDENTIFIER NULL,
[RequestData]       NVARCHAR (MAX)   NULL,
[ResponseData]      NVARCHAR (MAX)   NULL,
[UserName]          NVARCHAR (50)    NULL,
[AccountName]       NVARCHAR (100)   NULL,
[AccountId]         INT              NULL,
[CreatedUserId]     INT              NULL,
[CreatedDate]       DATETIME         NULL,
[MethodName]        NVARCHAR (100)   NULL
)
AS
BEGIN
   
DECLARE @sqlScript NVARCHAR(1000)

IF(@searchString IS NOT NULL AND LEN(@searchString) > 0)
BEGIN
SET @sqlScript = N'SELECT * FROM 
                (
                 SELECT ROW_NUMBER() OVER (ORDER BY '+@orderBy+' '+@orderByDirection+') AS rn , * from LogAPICall where Provider like ''%'+@searchString+'%'' or MethodName like ''%'+@searchString+'%''
                ) as result where result.rn between '+ CONVERT(varchar,@displayStart) + ' and ' + CONVERT(varchar,@displayLength)
END
ELSE
BEGIN
SET @sqlScript = N'SELECT * FROM 
                (
                 SELECT ROW_NUMBER() OVER (ORDER BY '+@orderBy+' '+@orderByDirection+') AS RN , * FROM LogAPICall
                ) AS result WHERE result.rn between ' + CONVERT(varchar,@displayStart) + ' and ' + CONVERT(varchar,@displayLength)
END

 insert @logs
 Execute(@sqlScript)
return
END

When I run this code, it returns following error :

Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

Why?

Sorry, you cannot perform dynamic SQL in a function, period. You can re-write your function to not require dynamic SQL, but does that really solve the underlying problem? What will you do when you need a procedure and are still hand-cuffed by this completely unrelated Entity Framework issue involving return type? Clearly something is broken with the way you have configured EF or hooked up your procedure, otherwise a lot of people would be complaining that EF doesn't work with procedures that return result sets. Do you think that's likely?

CREATE FUNCTION dbo.GetLogApiCalls -- dbo prefix, always
(
  @displayStart     INT,
  @displayLength    INT, -- is this a page size, like 20, or @displayEnd?
  @searchString     NVARCHAR(1000),
  @orderBy          NVARCHAR(100),
  @orderByDirection VARCHAR(4)
)
RETURNS TABLE
AS -- make it an inline TVF. Multi-statement TVFs can be a perf nightmare.
RETURN 
(
  SELECT * FROM 
  (
    SELECT rn = ROW_NUMBER() OVER (ORDER BY 
     CASE @orderByDirection WHEN 'ASC' THEN
      CASE @orderBy WHEN N'Provider'     THEN Provider
                    WHEN N'RequestData'  THEN RequestData
                    WHEN N'ResponseData' THEN ResponseData
                    WHEN N'UserName'     THEN UserName
                    WHEN N'AccountName'  THEN AccountName
                    WHEN N'MethodName'   THEN MethodName
                    WHEN N'RequestIdentifier'
                                         THEN CONVERT(CHAR(36), RequestIdentifier)
                    WHEN N'CreatedDate'  THEN CONVERT(CHAR(23), CreatedDate, 126)
      END
     END,
     CASE @orderByDirection WHEN 'ASC' THEN
      CASE @orderBy WHEN N'Id'            THEN Id
                    WHEN N'AccountId'     THEN AccountId
                    WHEN N'CreatedUserId' THEN CreatedUserId
      END
     END,
     CASE @orderByDirection WHEN 'DESC' THEN
      CASE @orderBy WHEN N'Provider'     THEN Provider
                    WHEN N'RequestData'  THEN RequestData
                    WHEN N'ResponseData' THEN ResponseData
                    WHEN N'UserName'     THEN UserName
                    WHEN N'AccountName'  THEN AccountName
                    WHEN N'MethodName'   THEN MethodName
                    WHEN N'RequestIdentifier' 
                                         THEN CONVERT(CHAR(36), RequestIdentifier)
                    WHEN N'CreatedDate'  THEN CONVERT(CHAR(23), CreatedDate, 126)
      END
     END DESC,
     CASE @orderByDirection WHEN 'DESC' THEN
      CASE @orderBy WHEN N'Id'            THEN Id
                    WHEN N'AccountId'     THEN AccountId
                    WHEN N'CreatedUserId' THEN CreatedUserId
      END
     END DESC), [Id],[Provider],[RequestIdentifier],[RequestData],
     [ResponseData],[UserName],[AccountName],[AccountId],
     [CreatedUserId],[CreatedDate],[MethodName]
  FROM dbo.LogAPICall
  WHERE LEN(@searchString) = 0 OR 
  (
    @searchString > ''  AND 
    (   
         Provider   LIKE '%' + @searchString + '%'
      OR MethodName LIKE '%' + @searchString + '%'
    )
  )
) AS x 
WHERE rn BETWEEN @displayStart AND @displayStart + @displayLength - 1 -- assumption
);

This dynamic ORDER BY is the devil, though. Even as an inline TVF this will only perform well for different parameters by taking a hit and adding OPTION (RECOMPILE) in any referencing query. Solution? Use a stored procedure with dynamic SQL, and figure out your Entity Framework configuration issue separately. As a stored procedure this would be so much better as:

CREATE PROCEDURE dbo.GetLogApiCalls -- dbo prefix, always
  @displayStart     INT,
  @displayLength    INT, -- is this a page size, like 20, or @displayEnd?
  @searchString     NVARCHAR(1000),
  @orderBy          NVARCHAR(100),
  @orderByDirection VARCHAR(4)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM (
    SELECT rn = ROW_NUMBER() OVER (ORDER BY ' 
        + @orderBy + ' ' + @orderByDirection + '), 
       [Id],[Provider],[RequestIdentifier],[RequestData],
       [ResponseData],[UserName],[AccountName],[AccountId],
       [CreatedUserId],[CreatedDate],[MethodName]
    FROM dbo.LogAPICall'
    + CASE WHEN LEN(@searchString) > 0 THEN
    ' WHERE Provider LIKE ''%'' + @searchString + ''%''
       OR MethodName LIKE ''%'' + @searchString + ''%'''
       ELSE '' END
    + ') AS x 
       WHERE rn BETWEEN @displayStart 
         AND @displayStart + @displayLength - 1;';

  DECLARE @params NVARCHAR(MAX) = N'@searchString NVARCHAR(1000),'
    + '@displayStart INT, @displayLength INT';

  EXEC sp_executesql @sql, @params, @searchString, @displayStart, @displayLength;
END
GO

Especially if you have the setting optimize for ad hoc workloads enabled. Now, this is still prone to SQL injection due to the dynamic ORDER BY, which can't be parameterized, so you may want to add validation that these parameters only contain valid values.