0

There is an answer on ASP.NET forums that seems to imply it's possible to get around the 'no stored procedures in functions' rule without using OPENROWSET. The code from the link is below:

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

I get this error when I try to use it:

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

This is my 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)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
VSO
  • 11,546
  • 25
  • 99
  • 187
  • 2
    Read the correct answer further down in that thread: "sprocs can't be called from TVFs except through massive hackery, and generally the hackery is inadvisable". – Gordon Linoff Oct 04 '17 at 20:18
  • 1
    Additional info on the indavisable hackery here: https://stackoverflow.com/questions/6344880/execute-stored-procedure-from-a-function – Jacob H Oct 04 '17 at 20:31
  • 1
    Error message has the clue: "side-effect". A function cannot have any - for SPs they cannot be ruled out. – devio Oct 04 '17 at 20:34
  • Actually, I could just use the function, but it can't do dynamic sql without running exec...which runs a stored proc...good times. – VSO Oct 04 '17 at 20:37

0 Answers0