title: Is it possible to call a stored procedure from a function in SQL?
body: <p>There is an <a href="https://forums.asp.net/t/1894267.aspx?how%20to%20exec%20stored%20procedure%20in%20table%20valued%20function%20" rel="nofollow noreferrer">answer</a> on ASP.NET forums that seems to imply it's possible to get around the 'no stored procedures in functions' rule without using <a href="https://technet.microsoft.com/en-us/library/ms190312(v=sql.110).aspx" rel="nofollow noreferrer"><code>OPENROWSET</code></a>. The code from the link is below:</p>
  
  <pre><code>create function demofunc(@input varchar(200))
  returns table
  as
  begin
      declare @string1 varchar(100);
      declare @finalstring as varchar(100);
  
      set @string1 = '%';
      set @finalstring = @input + @string1;
  
      declare @table as table (define your table here)
  
      insert into @table
          EXEC sp_FindStringKeyInTable '', 'dbo', 'resultCustKeywordSearchView'
  
      select * from @table
  
      return
    end
  </code></pre>
  
  <p>I get this error when I try to use it:</p>
  
  <blockquote>
    <p>Invalid use of a side-effecting operator 'INSERT EXEC' within a function.</p>
  </blockquote>
  
  <p>This is my code:</p>
  
  <pre><code>CREATE FUNCTION dbo.crds_GetFormAnswer
       (@FieldName varchar(max), 
        @TableName varchar(max), 
        @PrimaryKeyColumnName varchar(max), 
        @DataRecordId int)  
  RETURNS varchar(max)
  AS
  BEGIN
      DECLARE @temp_table AS TABLE (Form_Answers varchar(max))
  
      INSERT INTO @temp_table (Form_Answers)
      --VALUES ( '12345' ) //This code works
         EXEC crds_sp_GetFormAnswer @FieldName = 'Submission_Data', @TableName = 'cr_Submissions',@PrimaryKeyColumnName = 'Submission_ID', @DataRecordId = 15;
  
      RETURN (select TOP 1 Form_Answers from @temp_table)
  END 
  GO  
  
  SELECT [dbo].crds_GetFormAnswer('Submission_Data', 'cr_Submissions', 'Submission_ID', 15)
  </code></pre>