**1.
UDF to Add Two Numbers**
CREATE FUNCTION AddNumbers (@num1 INT, @num2 INT)
RETURNS INT
AS
BEGIN
RETURN (@num1 + @num2)
END
-- Example usage:
SELECT dbo.AddNumbers(2, 3); -- Output: 5
**2. UDF to Subtract Two Numbers**
CREATE FUNCTION SubtractNumbers (@num1 INT, @num2 INT)
RETURNS INT
AS
BEGIN
RETURN (@num1 - @num2)
END
-- Example usage:
SELECT dbo.SubtractNumbers(7, 5); -- Output: 2
**3. UDF to Multiply Two Numbers**
CREATE FUNCTION MultiplyNumbers (@num1 INT, @num2 INT)
RETURNS INT
AS
BEGIN
RETURN (@num1 * @num2)
END
-- Example usage:
SELECT dbo.MultiplyNumbers(4, 5); -- Output: 20
**4. UDF to Divide Two Numbers**
CREATE FUNCTION DivideNumbers (@num1 INT, @num2 INT)
RETURNS FLOAT
AS
BEGIN
RETURN (@num1 * 1.0 / @num2)
END
-- Example usage:
SELECT dbo.DivideNumbers(10, 2); -- Output: 5.0
**5. UDF to Check If a Number is Even**
CREATE FUNCTION IsEven (@number INT)
RETURNS BIT
AS
BEGIN
IF @number % 2 = 0
RETURN 1
ELSE
RETURN 0
END
-- Example usage:
SELECT dbo.IsEven(4); -- Output: 1 (Even)
**6. UDF to Check If a Number is Odd**
CREATE FUNCTION IsOdd (@number INT)
RETURNS BIT
AS
BEGIN
IF @number % 2 = 1
RETURN 1
ELSE
RETURN 0
END
-- Example usage:
SELECT dbo.IsOdd(3); -- Output: 1 (Odd)
**7. UDF to Get the Square of a Number**
CREATE FUNCTION GetSquare (@number INT)
RETURNS INT
AS
BEGIN
RETURN (@number * @number)
END
-- Example usage:
SELECT dbo.GetSquare(5); -- Output: 25
**8. UDF to Get the Cube of a Number**
CREATE FUNCTION GetCube (@number INT)
RETURNS INT
AS
BEGIN
RETURN (@number * @number * @number)
END
-- Example usage:
SELECT dbo.GetCube(2); -- Output: 8
**9. UDF to Find Maximum of Two Numbers**
CREATE FUNCTION MaxNumber (@num1 INT, @num2 INT)
RETURNS INT
AS
BEGIN
IF @num1 > @num2
RETURN @num1
ELSE
RETURN @num2
END
-- Example usage:
SELECT dbo.MaxNumber(3, 7); -- Output: 7
**10. UDF to Find Minimum of Two Numbers**
CREATE FUNCTION MinNumber (@num1 INT, @num2 INT)
RETURNS INT
AS
BEGIN
IF @num1 < @num2
RETURN @num1
ELSE
RETURN @num2
END
-- Example usage:
SELECT dbo.MinNumber(3, 7); -- Output: 3
**11. UDF to Calculate Factorial of a Number**
CREATE FUNCTION Factorial (@number INT)
RETURNS INT
AS
BEGIN
DECLARE @result INT = 1
WHILE @number > 1
BEGIN
SET @result = @result * @number
SET @number = @number - 1
END
RETURN @result
END
-- Example usage:
SELECT dbo.Factorial(4); -- Output: 24
**12. UDF to Find the Power of a Number**
CREATE FUNCTION PowerNumber (@base INT, @exponent INT)
RETURNS INT
AS
BEGIN
RETURN (POWER(@base, @exponent))
END
-- Example usage:
SELECT dbo.PowerNumber(2, 3); -- Output: 8
**13. UDF to Concatenate First and Last Name**
CREATE FUNCTION FullName (@firstName VARCHAR(50), @lastName VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
RETURN (@firstName + ' ' + @lastName)
END
-- Example usage:
SELECT dbo.FullName('John', 'Doe'); -- Output: John Doe
**14. UDF to Check if a Number is Prime**
CREATE FUNCTION IsPrime (@number INT)
RETURNS BIT
AS
BEGIN
DECLARE @i INT = 2
IF @number <= 1
RETURN 0
WHILE @i <= @number / 2
BEGIN
IF @number % @i = 0
RETURN 0
SET @i = @i + 1
END
RETURN 1
END
-- Example usage:
SELECT dbo.IsPrime(5); -- Output: 1 (Prime)
**15. UDF to Convert Celsius to Fahrenheit**
CREATE FUNCTION CelsiusToFahrenheit (@celsius FLOAT)
RETURNS FLOAT
AS
BEGIN
RETURN (@celsius * 9 / 5) + 32
END
-- Example usage:
SELECT dbo.CelsiusToFahrenheit(25); -- Output: 77
**16. UDF to Convert Fahrenheit to Celsius**
CREATE FUNCTION FahrenheitToCelsius (@fahrenheit FLOAT)
RETURNS FLOAT
AS
BEGIN
RETURN (@fahrenheit - 32) * 5 / 9
END
-- Example usage:
SELECT dbo.FahrenheitToCelsius(77); -- Output: 25
**17. UDF to Reverse a String**
CREATE FUNCTION ReverseString (@input VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @reversed VARCHAR(100) = ''
DECLARE @i INT = LEN(@input)
WHILE @i > 0
BEGIN
SET @reversed = @reversed + SUBSTRING(@input, @i, 1)
SET @i = @i - 1
END
RETURN @reversed
END
-- Example usage:
SELECT dbo.ReverseString(''); -- Output: LQS
**18. UDF to Count Vowels in a String**
CREATE FUNCTION CountVowels (@input VARCHAR(100))
RETURNS INT
AS
BEGIN
DECLARE @vowelCount INT = 0
DECLARE @i INT = 1
WHILE @i <= LEN(@input)
BEGIN
IF SUBSTRING(@input, @i, 1) IN ('a', 'e', 'i', 'o', 'u', 'A', 'E', 'I',
'O', 'U')
SET @vowelCount = @vowelCount + 1
SET @i = @i + 1
END
RETURN @vowelCount
END
-- Example usage:
SELECT dbo.CountVowels('Programming'); -- Output: 3
**19. UDF to Count Consonants in a String**
CREATE FUNCTION CountConsonants (@input VARCHAR(100))
RETURNS INT
AS
BEGIN
DECLARE @consonantCount INT = 0
DECLARE @i INT = 1
WHILE @i <= LEN(@input)
BEGIN
IF SUBSTRING(@input, @i, 1) NOT IN ('a', 'e', 'i', 'o', 'u', 'A', 'E', 'I',
'O', 'U')
SET @consonantCount = @consonantCount + 1
SET @i = @i + 1
END
RETURN @consonantCount
END
-- Example usage:
SELECT dbo.CountConsonants('Programming'); -- Output: 7
**20. UDF to Calculate the Area of a Circle**
CREATE FUNCTION AreaOfCircle (@radius FLOAT)
RETURNS FLOAT
AS
BEGIN
RETURN (3.14159 * @radius * @radius)
END
-- Example usage:
SELECT dbo.AreaOfCircle(7); -- Output: 153.938
**21. UDF to Calculate the Area of a Rectangle**
CREATE FUNCTION AreaOfRectangle (@length FLOAT, @width FLOAT)
RETURNS FLOAT
AS
BEGIN
RETURN (@length * @width)
END
-- Example usage:
SELECT dbo.AreaOfRectangle(10, 5); -- Output: 50
**22. UDF to Calculate the Area of a Triangle**
CREATE FUNCTION AreaOfTriangle (@base FLOAT, @height FLOAT)
RETURNS FLOAT
AS
BEGIN
RETURN (0.5 * @base * @height)
END
-- Example usage:
SELECT dbo.AreaOfTriangle(10, 5); -- Output: 25
**23. UDF to Convert Hours to Minutes**
CREATE FUNCTION HoursToMinutes (@hours INT)
RETURNS INT
AS
BEGIN
RETURN (@hours * 60)
END
-- Example usage:
SELECT dbo.HoursToMinutes(2); -- Output: 120
**24. UDF to Get the Length of a
String**
CREATE FUNCTION GetStringLength (@input VARCHAR(100))
RETURNS INT
AS
BEGIN
RETURN (LEN(@input))
END
-- Example usage:
SELECT dbo.GetStringLength(' Server'); -- Output: 11
**25. UDF to Check If a Number is Even or Odd**
CREATE FUNCTION ChecEvenOdd (@number INT)
RETURNS VARCHAR(4)
AS
BEGIN
RETURN CASE
WHEN @number % 2 = 0 THEN 'Even'
ELSE 'Odd'
END
END
select dbo.ChecEvenOdd(6)
-- Example usage:
SELECT dbo.CheckEvenOdd(5); -- Output: Odd
or
CREATE FUNCTION CheckEvenOdd (@number INT)
RETURNS VARCHAR(4)
AS
BEGIN
DECLARE @result VARCHAR(4)
IF @number % 2 = 0
SET @result = 'Even'
ELSE
SET @result = 'Odd'
RETURN @result
END
SELECT dbo.CheckEvenOdd(5)
**26. UDF to Calculate the Square Root of a Number**
CREATE FUNCTION SquareRoot (@number FLOAT)
RETURNS FLOAT
AS
BEGIN
RETURN (SQRT(@number))
END
-- Example usage:
SELECT dbo.SquareRoot(25); -- Output: 5.0
**27. UDF to Find the Absolute Value of a Number**
CREATE FUNCTION AbsoluteValue (@number FLOAT)
RETURNS FLOAT
AS
BEGIN
RETURN (ABS(@number))
END
-- Example usage:
SELECT dbo.AbsoluteValue(-7); -- Output: 7
**28. UDF to Check If a Number is Negative**
CREATE FUNCTION IsNegative (@number INT)
RETURNS BIT
AS
BEGIN
IF @number < 0
RETURN 1
ELSE
RETURN 0
END
-- Example usage:
SELECT dbo.IsNegative(-5); -- Output: 1 (Negative)
**29. UDF to Find the Decimal Part of a Number**
CREATE FUNCTION DecimalPart (@number FLOAT)
RETURNS FLOAT
AS
BEGIN
RETURN (@number - FLOOR(@number))
END
-- Example usage:
SELECT dbo.DecimalPart(7.89); -- Output: 0.89
**30. UDF to Count the Words in a String**
CREATE FUNCTION CountWords (@input VARCHAR(100))
RETURNS INT
AS
BEGIN
DECLARE @wordCount INT = 0
DECLARE @i INT = 1
WHILE @i <= LEN(@input)
BEGIN
IF SUBSTRING(@input, @i, 1) = ' '
SET @wordCount = @wordCount + 1
SET @i = @i + 1
END
RETURN (@wordCount + 1)
END
-- Example usage:
SELECT dbo.CountWords(' Server Functions'); -- Output: 3