CODEWITHSUNDEEP
function
Rob Brown
Rob Brown

Reputation: 41

How to create a function is SQL Server 2008 R2?

I was able to create the stored procedure below, but now I am having some trouble dropping it and creating it as a function. Any help would be greatly appreciated:

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

When running this stored procedure:

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

Upvotes: 4

Views: 23083

Answers (1)

jorgebg
jorgebg

Reputation: 6600

Try this:

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)

Upvotes: 2

Related Questions