function - SQL Server 2008 R2如何创建函数？

Question

我能够创建下面的存储过程，但现在我在删除它并将其创建为函数时遇到一些问题。任何帮助将不胜感激:

CREATE PROC spTriangular
   @ValueIn int,
   @ValueOut int OUTPUT
AS
   DECLARE @InWorking int
   DECLARE @OutWorking int

   IF @ValueIn != 1
   BEGIN
         SELECT @InWorking = @ValueIn - 1
         EXEC spTriangular @InWorking, @OutWorking OUTPUT

         SELECT @ValueOut = @ValueIn + @OutWorking
   END
   ELSE
   BEGIN
         SELECT @ValueOut = 1
   END

   RETURN
GO

运行此存储过程时:

DECLARE @WorkingOut int
DECLARE @WorkingIn int

SELECT @WorkingIn = 5

EXEC spTriangular @WorkingIn, @WorkingOut OUTPUT

PRINT CAST(@WorkingIn AS varchar) + ' Triangular is ' + CAST(@WorkingOut AS  varchar)
GO

Triangular is 15

Answer 1

试试这个:

CREATE FUNCTION dbo.spTriangular
(@ValueIn int)
RETURNS  int
AS
  DECLARE @ValueOut int
  DECLARE @InWorking int
  DECLARE @OutWorking int
  IF @ValueIn != 1
  BEGIN
           SELECT @InWorking = @ValueIn - 1
            SELECT @OutWorking = dbo.spTriangular(@InWorking)

            SELECT @ValueOut = @ValueIn + @OutWorking
  END
  ELSE
  BEGIN
            SELECT @ValueOut = 1
  END
  RETURN(@ValueOut)