title: Return result set from function with dynamic query
body: <p>I am trying to return result set from function which use executes a command string with Execute statement.</p>
  <pre><code>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) &gt; 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
  </code></pre>
  <p>When I run this code, it returns following error :</p>
  <blockquote>
  <p>Invalid use of a side-effecting operator 'INSERT EXEC' within a function.</p>
  </blockquote>
  <p>Why?</p>