SQL – User Define Functions ( Scalar and Table output)

Spread the love

SQL – UDF – allows to create/define  a functions to meet specific needs or  simplify complex SQL query.

Return a Table output – table can be defined and insert into table or a table out can be created

Scalar function – it allows a user to use it anywhere or in a another query, also multiple arguments can be created as per needs. it makes easier manipulation on  columns.

ITEM_TYPE TABLE

S_NOPRODUCTTYPE
1NIKESHOES
2REEBOKTSHIRT
3FILASHOES
4PUMARUNNING SHOES
5ADIDASSHOES

BRAND TABLE

S_NOPRODUCTMONTHYEARQTYSALES
1NIKEDEC2016107599
2REEBOKJAN2014181200
3FILAFEB201786000
4PUMAJAN2015165000
5NULLAPR201252500

TABLE OUT UDF FUNCTION

CREATE FUNCTION GET_YEAR
            (     
                  — Add the parameters for the function here
                   @YEAR INTEGER
            )  

                RETURNS TABLE
                   AS
               RETURN

       (
              SELECT A.S_NO , A.PRODUCT , B.TYPE from BRAND AS A
              LEFT JOIN
              ITEM_TYPES B
              ON A.PRODUCT = B.PRODUCT
              WHERE A.YEAR = @YEAR
           )

USE   –  SELECT * FROM GET_YEAR(2016)

 

FUNCTION FOR TABLE VIEW

 

 

 

Scalar valued Function

CREATE FUNCTION dbo.FIRST_NAME( @InputVal VARCHAR(MAX))

RETURNS VARCHAR(MAX)
AS

BEGIN
DECLARE @FVAL VARCHAR(MAX); — Defined a variable to store the value

SELECT @FVAL =
LEFT(@InputVal, LEN(@InputVal) – CHARINDEX(‘ ‘ , @InputVal)) ;

— if any null value then defined as 0 else Result value
IF (@FVAL IS NULL)
SET @FVAL = 0;
RETURN @FVAL;
END

 

USE – SELECT DBO.FIRST_NAME(‘RAJIV KUMAR’) AS FIRST_NAME

 

 

SCALAR FUNCTION