I was getting the same error, but strangely, all I did was add DISTINCT to my query and it worked. I'm not going to research why, but that might...

You can, however, assign an output variable using sp_executesql, but you can't use sp_executesql in a function. 🙂

Using EXEC is easier, but you can't hold the result of EXEC in a variable. This is a no-no:

set @result = EXEC('SELECT 4+5*6-3' )

The submitted function will...