Create Function TSQL Tutorial

1. Create Function Example

CREATE FUNCTION CtrAmount ( @Ctr_Id int(10) )
  RETURNS MONEY
  AS
  BEGIN
      DECLARE @CtrPrice MONEY
        SELECT @CtrPrice = SUM(amount)
          FROM Contracts
        WHERE contract_id = @Ctr_Id
      RETURN(@CtrPrice)
  END
GO

SELECT * FROM CtrAmount(345)
GO


2. Create Function Example

CREATE FUNCTION function_name (@PRODUCT_ID Int)
  RETURNS @ProductsList Table
    (Product_Id Int,
     Product_Dsp nvarchar(150),
     Product_Price Money )
AS
  BEGIN
    IF @PRODUCT_ID IS NULL
      BEGIN
        INSERT INTO @ProductsList (Product_Id, Product_Dsp, Product_Price)
        SELECT Product_Id, Product_Dsp, Product_Price
        FROM Products
      END
    ELSE
      BEGIN
        INSERT INTO @ProductsList (Product_Id, Product_Dsp, Product_Price)
        SELECT Product_Id, Product_Dsp, Product_Price
        FROM Products
        WHERE Product_Id = @PRODUCT_ID
      END
    RETURN
  END
GO