User Defined Functions (UDF)

What are User Defined Functions (UDF) in SQL Server?

Types of User Defined Functions in SQL Server?

Difference between Function and Stored Procedure?

Difference between Function and Stored Procedure?

• User Defined Function (UDF) can be used in the SQL statements anywhere in the SELECT, WHERE, and HAVING section where as Stored procedures cannot be used.
• Functions are designed to send their output to a query or T-SQL statement while Stored Procedures use EXECUTE or EXEC to run.
• We can not use EXECUTE and PRINT commands inside a function but in SPROC
• UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
• UDFs can't change the server environment or your operating system environment, while a SPROC can.
• Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
• Stored Procedures are stored in compiled format in the database where as Functions are compiled and excuted runtime.
• SPROC can be used with XML FOR Clause but Functions can not be.
• SPROC can have transaction but not Functions.
• Functions can be used in a SPROC but SPROC cann't be used in a Function. Only extended stored procedures can be called from a function.
• Of course there will be Syntax differences and here is a sample of that

CREATE PROCEDURE dbo.ProcedureName
(
   @parameter1 datatype = DefaultValue,
   @parameter2 datatype OUTPUT
)
AS 
BEGIN
   T-SQL statements
   RETURN
END
GO

CREATE FUNCTION dbo.FunctionName
(
   @parameter1 datatype = DefaultValue,
   @parameter2 datatype
)
RETURNS datatype
AS
BEGIN
   SQL Statement
   RETURN Value
END
GO

Stored Procedure

What is Stored Procedure?

Difference Between Function and Stored Procedure?


 

Function to Convert Decimal Number into Binary, Ternary, and Octal

In this article I am sharing user defined function to convert a decimal number into Binary, Ternary, and Octalequivalent.

IF OBJECT_ID(N'dbo.udfGetNumbers', N'TF') IS NOT NULL
DROP FUNCTION dbo.udfGetNumbers
GO


CREATE FUNCTION dbo.udfGetNumbers
(@base [int], @lenght [int])
RETURNS @NumbersBaseN TABLE
(
  decNum [int] PRIMARY KEY NOT NULL,
  NumBaseN [varchar](50) NOT NULL
)
AS
BEGIN
  WITH tblBase AS
  (
    SELECT CAST(0 AS VARCHAR(50)) AS baseNum
    UNION ALL
    SELECT CAST((baseNum + 1) AS VARCHAR(50))
    FROM tblBase WHERE baseNum < @base-1
  ),
  numbers AS
  (
    SELECT CAST(baseNum AS VARCHAR(50)) AS num
    FROM tblBase
    UNION ALL
    SELECT CAST((t2.baseNum + num) AS VARCHAR(50))
    FROM numbers CROSS JOIN tblBase t2
    WHERE LEN(NUM) < @lenght
  )

  INSERT INTO @NumbersBaseN
  SELECT ROW_NUMBER() OVER (ORDER BY NUM) -1 AS rowID, NUM
  FROM numbers WHERE LEN(NUM) > @lenght - 1

  OPTION (MAXRECURSION 0);
  RETURN
END
GO


-- Unit Test --
-- Example with decimal, binary, ternary and octal


SELECT
   U1.decNum   AS Base10,
   U1.NumBaseN AS Base2,
   U2.NumBaseN AS Base3,
   U3.NumBaseN AS Base8
FROM dbo.udfGetNumbers(2, 10) U1
JOIN dbo.udfGetNumbers(3, 7) U2
  ON u1.decNum = u2.decNum
JOIN dbo.udfGetNumbers(8, 4) U3
  ON u2.decNum = u3.decNum


Here is the output: