how to create scalar function in sql

Total Post:3

Points:21
 168  View(s)
Ratings:
Rate this:

i have need to create scalar function in sql.

please help me.

  1. Post:281

    Points:2003
    Re: how to create scalar function in sql

    Here we create a user-defined (Scalar-valued function) which remove html tag from string. It is a simple function that need a string parameter;

    Example Of  SQL  Functions 

    CREATE FUNCTION [dbo].[RemoveHtmlFronString] (@HTMLText VARCHAR(MAX))
    
    RETURNS VARCHAR(MAX)
    AS
    BEGIN
    DECLARE @Start INT
    DECLARE @End INT
    DECLARE @Length INT
    SET @Start = CHARINDEX('<',@HTMLText) SET @End =
    CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
    SET @Length = (@End - @Start) + 1 WHILE @Start > 0
    AND @End > 0
    AND @Length > 0
    BEGIN
    SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
    SET @Start = CHARINDEX('<',@HTMLText) SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
    SET @Length = (@End - @Start) + 1
    END
    RETURN LTRIM(RTRIM(@HTMLText))
    END

    --USE

     PRINT [dbo].[RemoveHtmlFronString]('<b> Hello</b>')

  1. Post:140

    Points:984
    Re: how to create scalar function in sql

    A CREATE FUNCTION(scalar):

    - Specify a name for the function.

    - data type for each input parameter.

    - RETURNS keyword and the data type of the scalar return value.

    Example : 

    CREATE FUNCTION GetPrice (Vendor CHAR(20), Pid INT)          
        RETURNS DECIMAL(10,3)
        LANGUAGE SQL
        MODIFIES SQL
        BEGIN
          DECLARE price DECIMAL(10,3);
          IF Vendor = 'Vendor 1'
            THEN SET price = (SELECT ProdPrice FROM V1Table WHERE Id = Pid);
          ELSE IF Vendor = 'Vendor 2'
            THEN SET price = (SELECT Price
                              FROM V2Table
                 WHERE Pid = GetPrice.Pid);
          END IF;
      RETURN price;
      END
    SELECT dbo.function (parameters)

Answer