SQL Server - Function with procedures

Question

I want to create this function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION fnCalcTrimmean
(   
    @starttime datetime,
    @endtime datetime,
    @percentile decimal(18, 2),
    @platform varchar(100),
    @stage varchar(50)
)
RETURNS @result TABLE 
(
    TimeResult DECIMAL(18, 2)
)
AS
BEGIN

    INSERT INTO @result
    EXEC spCalcTrimmean @starttime, @endtime, @percentile, @platform, @stage

    RETURN
END

But when I try create the function the error appears:

Msg 443, Level 16, State 14, Procedure fnCalcTrimmean, Line 17
Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

[UPDATE]

The procedure spCalcTrimmean return ALWAYS a single value

What can I do to create a function like this?

Thanks :)

Remove the function and just call the proc, storing it's results in a temp table? Seems logical...[here's an example](https://stackoverflow.com/q/653714/6167855) — S3S, Jun 19 '17 at 19:11
@scsimon yeah, I know I can do this, but I need the result into a select statement and a procedure can't be inside a select, but a function can — Jose Felipe Blum de Araujo, Jun 19 '17 at 19:12
what do you mean "you need the result into a select statement". Storing the results in a temp table means you can select, join, etc to it all you want. Sounds like an [XY Problem](http://xyproblem.info/) — S3S, Jun 19 '17 at 19:13
Perhaps you can change the stored proc to be an inline table valued function instead of a procedure. Can you share the procedure code? — Sean Lange, Jun 19 '17 at 19:13
@SeanLange sorry, but I can't share this code cause was developed by another guy and my user don't have the access to modify the procedure =/ — Jose Felipe Blum de Araujo, Jun 19 '17 at 19:15
i fail to understand what you mean by "i need the result into a select statement " — S3S, Jun 19 '17 at 19:16
I wasn't suggesting you modify the procedure. i was suggesting that perhaps it could be turned into an inline table valued function. The existing procedure would remain untouched. — Sean Lange, Jun 19 '17 at 19:26

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

0

Answer is "nothing", since user functions are not intended to do such thing as an INSERT:

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql

Limitations and Restrictions

User-defined functions cannot be used to perform actions that modify the database state.

You cannot INSERT, UPDATE or DELETE from within a user-function.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 19 '17 at 19:25

L. Shimizu

11
5

score 0 · Answer 2 · answered Jun 19 '17 at 20:09

Apologies as I do not have high enough reputation to comment at this point.

If you cannot access the procedures code, then the only way around is to execute the procedure directly. In which case what is restricting you from calling it directly? Why does it have to be a select statement?

SQL Server - Function with procedures

2 Answers2