How do I get an stored procedure output into a variable inside a function in T-SQL?

Question

I've got a task which can be only accomplished by construction a QUERY at runtime and executing it with sp_executesql. The result has to be a boolean (0/1 integer) value which I need to return as a result of the function.

The only way I found to capture an SP's output is "INSERT INTO [table] EXECUTE [sp]" query, but functions forbid this.

Any ideas how to overcome this?

Answer 1

sp_executesql allows you to pass in parameters, but also to declare parameters as being for output. Just have a look at this simple example that is not very dynamic, but shows how output parameters for sp_executesql work

declare @sql nvarchar(max)
declare @user sysname

select @sql = 'SELECT @user = SYSTEM_USER'

exec sp_executesql @sql, N'@user sysname OUTPUT', @user OUTPUT

select @user