how to create scalar function in sql

Question

Prakash nidhi Verma · Answer

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

Anonymous User · Answer

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 Ofand  SQLand  Functionsand CREATE FUNCTION [dbo].[RemoveHtmlFronString] (@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
SET @Start = CHARINDEX('andlt;',@HTMLText) SET @End = 
CHARINDEX('andgt;',@HTMLText,CHARINDEX('andlt;',@HTMLText)) 
SET @Length = (@End - @Start) + 1 WHILE @Start andgt; 0
AND @End andgt; 0
AND @Length andgt; 0
BEGIN
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
SET @Start = CHARINDEX('andlt;',@HTMLText) SET @End = CHARINDEX('andgt;',@HTMLText,CHARINDEX('andlt;',@HTMLText))
SET @Length = (@End - @Start) + 1
END
RETURN LTRIM(RTRIM(@HTMLText))
END
--USE
and PRINT [dbo].[RemoveHtmlFronString]('andlt;bandgt; Helloandlt;/bandgt;')

forum

how to create scalar function in sql

Anonymous User

Can you answer this question?

2 Answers

Liked By