call store procedure from function in sql server

Function dbo.yourFunctionName
(
yourPara
)
RETURN TABLE
AS
RETURN
(
SELECT * FROM OPENROWSET('SQLNCLI','Server=yourServerName;UID=yourUserName;Pwd=yourPassword;','SET FMTONLY OFF; EXEC yourDataBase.dbo.yourSpName @yourPara') as C
)

Read More