Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

forum

home / developersection / forums / how to create scalar function in sql
Ask Question
Anonymous User
Anonymous User

Other

I am a content writter !

Message

2 Answers

Prakash nidhi Verma
Prakash nidhi Verma 10 Jul 2018
Report Abuse

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)
Anonymous User
Anonymous User 09 Jul 2018
Report Abuse

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>')
advertising