Search code examples
sqlsql-servert-sqlstored-proceduressql-function

Function calling procedure gives error "Only functions and some extended stored procedures can be executed from within a function"


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?


Solution

  • 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