SQL Functions


A SQL Function is same as a stored procedure that must return a value.
The various type of user defined functions are:
·      Scalar Function – The function takes one OR many input values, but returns a single data value.
·     Table-Valued Functions – The function takes one OR many input parameters and  must return the columns of a table.

Limitations and restrictions of Function

  • SET statements are not allowed in a user-defined function.

  • User-defined functions cannot be used to perform actions that modify the database state.

  • User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target.

  • User-defined functions cannot return multiple result sets. Use a stored procedure if you need to return multiple result sets.

  • Error handling is restricted in a user-defined function. A UDF does not support PRINT, TRY…CATCH, @ERROR or RAISERROR.

  • User-defined functions cannot call a stored procedure, but can call an extended stored procedure.

  • User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.

  • User-defined functions can be nested; that is, one user-defined function can call another. The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. User-defined functions can be nested up to 32 levels. Exceeding the maximum levels of nesting causes the whole calling function chain to fail. Any reference to managed code from a Transact-SQL user-defined function counts as one level against the 32-level nesting limit. Methods invoked from within managed code do not count against this limit.

The following Service Broker statements cannot be included in the definition of a Transact-SQL user-defined function:


  • BEGIN DIALOG CONVERSATION
  • END CONVERSATION
  • GET CONVERSATION GROUP
  • MOVE CONVERSATION
  • RECEIVE
  • SEND

CREATE Function

Function consists of:
·         Header comments, Which includes author, date, purpose and change history         with example.
·         Function Name.
·         Input parameters, Output parameters if any.
·         Body of Stored Procedure.

1.     BEGIN
2.     DECLARE Local variables
3.     T-SQL [Collection of SQL Statements]
4.     END

EXAMPLE: SCALAR FUNCTION

-- ======================================================
/*

AUTHOR       : Govind
CREATE DATE  : 16-Oct-2014
DESCRIPTION  : To convert amount to word.

SET STATISTICS TIME ON
SELECT [dbo].[UDF_PT_ConvertAmountToWord](12345678901234.78, @@NESTLEVEL ,1, 1)
SELECT [dbo].[UDF_PT_ConvertAmountToWord](0, @@NESTLEVEL, 1, 1)
SELECT [dbo].[UDF_PT_ConvertAmountToWord](0.49, @@NESTLEVEL, 1, 1)
SELECT [dbo].[UDF_PT_ConvertAmountToWord](0.49, @@NESTLEVEL, 2, 1)
SELECT [dbo].[UDF_PT_ConvertAmountToWord](-0.49, @@NESTLEVEL, 1, 1)
SELECT [dbo].[UDF_PT_ConvertAmountToWord](-0.49, @@NESTLEVEL, 2, 1)
SELECT [dbo].[UDF_PT_ConvertAmountToWord](0.50, @@NESTLEVEL, 1, 1)
SELECT [dbo].[UDF_PT_ConvertAmountToWord](0.50, @@NESTLEVEL, 2, 1)
SELECT [dbo].[UDF_PT_ConvertAmountToWord](-0.50, @@NESTLEVEL, 1, 1)
SELECT [dbo].[UDF_PT_ConvertAmountToWord](-0.50, @@NESTLEVEL, 2, 1)
SELECT [dbo].[UDF_PT_ConvertAmountToWord](10012.44, @@NESTLEVEL, 1, 1)
SELECT [dbo].[UDF_PT_ConvertAmountToWord](10012.44, @@NESTLEVEL, 2, 1)
SELECT [dbo].[UDF_PT_ConvertAmountToWord](-12.44, @@NESTLEVEL, 1, 1)
SELECT [dbo].[UDF_PT_ConvertAmountToWord](12345678901234.78, @@NESTLEVEL, 2, 2)


SET STATISTICS TIME OFF

*/
-- ======================================================
CREATE FUNCTION [dbo].[UDF_PT_ConvertAmountToWord]
(
@Money AS MONEY,
@NestLevel AS INT     = 0,    -- Always pass @@NESTLEVEL instead of any value
@Type AS SMALLINT     = 1,    -- 1 Cents eg. [Twelve Dollars 44/100 Cents], 2-Dollars eg. [Twelve and 44/100 Dollars]
@FirstTime AS TINYINT = 1     -- By default 1 when calling this function.
)

RETURNS VARCHAR(1024)
AS
BEGIN
          DECLARE @Number AS BIGINT
          DECLARE @MinusFlag AS BIT
          DECLARE @ZeroWord AS VARCHAR(10)=''
         
          -- Specify the currency name and coin name.
          DECLARE @CurrencyName AS VARCHAR(10)='Dollars'
          DECLARE @CoinName AS VARCHAR(10)='Cents'
         
          /*
          DECLARE @CurrencyName AS VARCHAR(20)='Rupees'
          DECLARE @CoinName AS VARCHAR(20)='Paise'
          */
         
          IF @Money = 0 AND ISNULL(@FirstTime,1) = 1
          BEGIN
                   RETURN 'Zero'
          END
         
          IF (ISNULL(@FirstTime,1) = 1 AND ((ROUND(@Money,0) = 0) OR (FLOOR(@Money) = 0) OR (CEILING(@Money) = 0)))
          BEGIN
                   SET @ZeroWord = 'Zero'
          END
         
          SET @FirstTime = 2
          IF @Money < 0
          BEGIN
                   SET @Money = -1 * @Money
                   SET @MinusFlag = 1
          END

          SET @Number = FLOOR(@Money)

          DECLARE @Below20 TABLE (ID INT IDENTITY(0,1), Word VARCHAR(32))
          DECLARE @Below100 TABLE (ID INT IDENTITY(2,1), Word VARCHAR(32))

          INSERT INTO @Below20 (Word) VALUES ('Zero')
          INSERT INTO @Below20 (Word) VALUES ('One')
          INSERT INTO @Below20 (Word) VALUES ('Two')
          INSERT INTO @Below20 (Word) VALUES ('Three')
          INSERT INTO @Below20 (Word) VALUES ('Four')
          INSERT INTO @Below20 (Word) VALUES ('Five')
          INSERT INTO @Below20 (Word) VALUES ('Six')
          INSERT INTO @Below20 (Word) VALUES ('Seven')
          INSERT INTO @Below20 (Word) VALUES ('Eight')
          INSERT INTO @Below20 (Word) VALUES ('Nine')
          INSERT INTO @Below20 (Word) VALUES ('Ten')
          INSERT INTO @Below20 (Word) VALUES ('Eleven')
          INSERT INTO @Below20 (Word) VALUES ('Twelve')
          INSERT INTO @Below20 (Word) VALUES ('Thirteen')
          INSERT INTO @Below20 (Word) VALUES ('Fourteen')
          INSERT INTO @Below20 (Word) VALUES ('Fifteen')
          INSERT INTO @Below20 (Word) VALUES ('Sixteen')
          INSERT INTO @Below20 (Word) VALUES ('Seventeen')
          INSERT INTO @Below20 (Word) VALUES ('Eighteen')
          INSERT INTO @Below20 (Word) VALUES ('Nineteen')

          INSERT INTO @Below100 (Word) VALUES ('Twenty')
          INSERT INTO @Below100 (Word) VALUES ('Thirty')
          INSERT INTO @Below100 (Word) VALUES ('Forty')
          INSERT INTO @Below100 (Word) VALUES ('Fifty')
          INSERT INTO @Below100 (Word) VALUES ('Sixty')
          INSERT INTO @Below100 (Word) VALUES ('Seventy')
          INSERT INTO @Below100 (Word) VALUES ('Eighty')
          INSERT INTO @Below100 (Word) VALUES ('Ninety')

          DECLARE @English VARCHAR(1024)
          SET @English = ''
          (
          SELECT @English =
          CASE
                   WHEN @Number = 0 THEN ''
                   WHEN @Number BETWEEN 1 AND 19
                   THEN (SELECT Word FROM @Below20 WHERE ID=@Number)
                  
                   WHEN @Number BETWEEN 20 AND 99
                   THEN (SELECT Word FROM @Below100 WHERE ID=@Number/10)+ + ' '+ -- '-' +
                   dbo.[UDF_PT_ConvertAmountToWord]( @Number % 10, @NestLevel, @Type ,2)

                   WHEN @Number BETWEEN 100 AND 999
                   THEN (dbo.[UDF_PT_ConvertAmountToWord]( @Number / 100, @NestLevel, @Type, 2))+' Hundred '+
                   dbo.[UDF_PT_ConvertAmountToWord]( @Number % 100, @NestLevel, @Type, 2)

                   WHEN @Number BETWEEN 1000 AND 999999
                   THEN (dbo.[UDF_PT_ConvertAmountToWord]( @Number / 1000, @NestLevel, @Type, 2))+' Thousand '+
                   dbo.[UDF_PT_ConvertAmountToWord]( @Number % 1000, @NestLevel, @Type, 2)

                   WHEN @Number BETWEEN 1000000 AND 999999999
                   THEN (dbo.[UDF_PT_ConvertAmountToWord]( @Number / 1000000, @NestLevel, @Type, 2))+' Million '+
                   dbo.[UDF_PT_ConvertAmountToWord]( @Number % 1000000, @NestLevel, @Type, 2)

                   WHEN @Number BETWEEN 1000000000 AND 999999999999
                   THEN (dbo.[UDF_PT_ConvertAmountToWord]( @Number / 1000000000, @NestLevel, @Type, 2))+' Billion '+
                   dbo.[UDF_PT_ConvertAmountToWord]( @Number % 1000000000, @NestLevel, @Type, 2)

                   WHEN @Number BETWEEN 1000000000000 AND 99999999999999
                   THEN (dbo.[UDF_PT_ConvertAmountToWord]( @Number / 1000000000000, @NestLevel, @Type, 2))+' Trillion '+
                   dbo.[UDF_PT_ConvertAmountToWord]( @Number % 1000000000000, @NestLevel, @Type, 2)

          ELSE
                   'INVALID INPUT'
          END
          )

          SELECT @English = RTRIM(@English)

          SELECT @English = RTRIM(LEFT(@English,LEN(@English)-1)) WHERE RIGHT(@English,1)='-'

         
          IF (@@NestLevel - @NestLevel) = 1 AND @English!='INVALID INPUT'
          BEGIN
                  
                   IF @Type = 1
                   BEGIN
                             SELECT @English = @ZeroWord + @English+' ' + @CurrencyName + ' and '
                             SELECT @English = @English+ CONVERT(VARCHAR,CONVERT(INT,100*(@Money - @Number))) +' ' + @CoinName 
                   END      
                   ELSE
                   BEGIN
                             IF @ZeroWord = ''
                                      SELECT @English = @English+' and '
                                     
                             SELECT @English = @English+ CONVERT(VARCHAR,CONVERT(INT,100*(@Money - @Number))) +'/100 ' + @CurrencyName
                   END                         

                   IF (@MinusFlag = 1)
                   BEGIN
                             SET @English = '( ' + @English + ' )'
                   END
                  
          END
          RETURN (@English)
END

GO

-- You can test the function passing differnet parameters as shown below.
SELECT [dbo].[UDF_PT_ConvertAmountToWord](-0.50, @@NESTLEVEL, 1, 1)
SELECT [dbo].[UDF_PT_ConvertAmountToWord](12345678901234.78, @@NESTLEVEL, 1, 2)

GO



EXAMPLE: TABLE VALUED FUNCTION

-- ======================================================
/*

AUTHOR             : Govind
CREATE DATE  : 19-Feb-2015
DESCRIPTION  :  OFAC Match DoubleFlip.
SET STATISTICS TIME ON

SET STATISTICS TIME OFF

SET STATISTICS TIME ON
SELECT * from [dbo].[UDF_PP_Payee_OFAC_Match_DoubleFlip] (4,6,'GOVIND','123456782910', 0, 0, 0)

SET STATISTICS TIME OFF
*/
-- ======================================================
ALTER FUNCTION [dbo].[UDF_PP_Payee_OFAC_Match_DoubleFlip]
(
          @Ndx4 AS INT,
          @PartialLen AS INT,
          @PartialName VARCHAR(500),
          @ExtraNamePos VARCHAR(11),
          @AndPos AS INT,
          @OrPos AS INT,
          @PlusPos AS INT
) 

RETURNS @Table TABLE (
          RecordID int IDENTITY,
          Ndx3 int,
          Ndx4 int,
          NameEnd int,
          ExtraNamePos varchar(500))
AS 

BEGIN
         
          DECLARE @Ndx3 AS INT
          DECLARE @NameEnd AS INT
          DECLARE @ArrayLength  AS INT = 3
         
          SET  @NameEnd = @PartialLen
          IF (@AndPos >= 1  AND @AndPos > [dbo].[UDF_PP_Payee_ExtraNamePos_Get](@ExtraNamePos, @Ndx4, 3))
          BEGIN
                   SET @NameEnd = @AndPos - 1
                   SET @ExtraNamePos = [dbo].[UDF_PP_Payee_ExtraNamePos_Update](@ExtraNamePos, @Ndx4+1, @ArrayLength, @AndPos + 4)
          END
          INSERT @Table VALUES(@Ndx3, @Ndx4, @NameEnd, @ExtraNamePos)

          RETURN

END

ALTER Function 

Example for modifying the existing Function using the ALTER FUNCTION command is given below:


EXAMPLE:

ALTER FUNCTION [dbo].[UDF_PT_ConvertAmountToWord]

EXECUTE Function


Executing FUNCTION consists of:
SELECT <<Function Name>> (Input Parameters)
EXAMPLE:

SELECT [dbo].[UDF_PT_ConvertAmountToWord](12345678901234.78, @@NESTLEVEL, 1, 2)

DROP Function 

Example for deleting the existing function using the DROP FUNCTION command is given below:


EXAMPLE 1:

DROP FUNCTION [dbo].[UDF_PP_ConvertAmountToWord]

EXAMPLE 2:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UDF_PT_ConvertAmountToWord]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UDF_PT_ConvertAmountToWord]
GO

RENAME Function 


Example for renaming the existing Function (from USP_PT_ConvertAmountToWord to USP_PT_ConvertAmountInWord) is given below:


IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UDF_PT_ConvertAmountInWord]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
AND EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UDF_PT_ConvertAmountToWord]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
EXEC sp_rename 'UDF_PT_ConvertAmountToWord', 'UDF_PT_ConvertAmountInWord'

Another way of renaming the existing Function (from USP_PT_ConvertAmountToWord to USP_PT_ConvertAmountInWord) is, CREATE a new Function, and then DROP the existing Function.

Query the list of Function


EXAMPLE:

SELECT * FROM sysobjects
WHERE xtype IN ('FN', 'IF', 'TF', 'FS', 'FT')

EXAMPLE:

SELECT DISTINCT so.name, so.xtype, sc.TEXT
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%Amount%'
AND so.xtype IN ('FN', 'IF', 'TF', 'FS', 'FT')

1 comment:

  1. Your blog is in a convincing manner, thanks for sharing such an information with lots of your effort and time
    sql dba training
    sql server dba online training

    ReplyDelete