I created a function like this:
CREATE FUNCTION [dbo].[GetAnnouncementID]()
RETURNS INT
AS
BEGIN
DECLARE @AnnouncementID int
EXEC @AnnouncementID = DB1.dbo.PROC_GetID
RETURN @AnnouncementID
END
GO
Then I tried to use select dbo.GetAnnouncementID()
to get the return value, but the error message is:
Only functions and some extended stored procedures can be executed from within a function.
How to fix it?
You cannot execute a stored procedure from within a function. Your only option is to change it to an SP and modify your code to call an SP as follows:
CREATE procedure [dbo].[GetAnnouncementID]()
AS
BEGIN
SET NOCOUNT ON;
DECLARE @AnnouncementID int;
EXEC @AnnouncementID = DB1.dbo.PROC_GetID;
SELECT @AnnouncementID;
RETURN 0;
END
GO