Can we call a stored proc from a function?

Question

Can we call a stored procedure from a function in SQL? What's the reason?

Answer 1

Quick answer: No.

Why: A stored procedure does not produce any output that can be re-used inside SQL.

Answer 2

Functions can only read data, they can't update or change anything. It follows that functions are not allowed to call stored procedures.

Answer 3

To be efficient a function should be deterministic, i.e. the output should only be depending on the input, so that the result can be cached.

If you want to call a stored procedure from a function, yout have to specifically make the function non-deterministic.

Answer 4

Yes.

You can do this with a bit of a hack involving openrowset but it's not recommended as it will open a new connection.

CREATE FUNCTION dbo.test ()
RETURNS  varchar(200)
AS
BEGIN
    RETURN (Select top 1 [Name] from 
OPENROWSET('SQLNCLI','Server=.\SQL2008;Trusted_Connection=yes;','SET NOCOUNT ON;SET FMTONLY OFF;EXEC MASTER..SP_HELP') 
)

END
GO

SELECT dbo.test()