How to execute procedure in function SQL Server

Question

I need this function:

create function test (@InVar int)
returns nvarchar(max) as
begin
  declare @OutVar nvarchar(max);
  exec someProcedure @InVar, @OutVar out;
  return @OutVar
end

I want :

select *, dbo.test(ID)
from SomeTable

Answer 1

It seems you want to execute stored procedure someProcedure from within function test. That's also what you explicitly ask.

That is not allowed, so you cannot do that. Functions in SQL Server are never allowed to have side effects.

As stated in Microsoft's documentation regarding User-Defined Functions:

The statements in a BEGIN...END block cannot have any side effects. Function side effects are any permanent changes to the state of a resource that has a scope outside the function such as a modification to a database table. The only changes that can be made by the statements in the function are changes to objects local to the function, such as local cursors or variables. Modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user are examples of actions that cannot be performed in a function.

Note

If a CREATE FUNCTION statement produces side effects against resources that do not exist when the CREATE FUNCTION statement is issued, SQL Server executes the statement. However, SQL Server does not execute the function when it is invoked.

So it seems you have to find another strategy to do what you want.

Answer 2

Your code is perfect but it is not runnable because

Only functions and some extended stored procedures can be executed from within a function.

I Suggest You Instead of SP implement the logic in the function itself.