A custom stored procedure is called in a SQL Server custom function, and after executing this function, the following prompt is issued: "Only functions and extended stored procedures can be executed from within the function."
Reason: The function can only use simple SQL statements, logical control statements, complicated stored procedures can not be called, in the function can not use execute sp_executesql or execute. The workaround changes the function to a stored procedure and then uses the stored procedure in another stored procedure as if it were called a function.
The following is a stored procedure that invokes an instance of another stored procedure, with parameters passed.
--Stored Procedure Sp_b
Create proc Sp_b
@A int,
@B int,
@c int Output
As
Set @c = @A + @B
Go
--Stored Procedure p_a
Create proc Sp_a
@A int,
@B int
As
DECLARE @c int
exec sp_b @A, @B, @c output
Print @c
Go
--Test
EXEC sp_a 3, 5
SQL Server only functions and extended stored procedures can be executed from within a function