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')
No comments:
Post a Comment