SQL server user defined functions with example

sql server user defined functions example

CREATE FUNCTION DBO.YOUR_FUNCTION_NAME(@PARAMETER_1  INT,@PARAMETER_2  INT)
RETURNS return type
AS
BEGIN
    Your all sql statement
    RETURN data
END

SELECT  DBO.YOUR_FUNCTION_NAME(value_1,value_2)    

Rename sql server user defined function

There is no way to rename the SQL server user defined function. So the technique is you can drop the function and create the function again with the new name. Make sure you copy everything from your function before drop.

DROP FUNCTION DBO.function_old_name
GO
CREATE FUNCTION DBO.function_new_name(old function params)
RETURNS return type of your old function
AS
BEGIN
    – everything from your old function
END    

Types of SQL server User Defined functions

There are two types of sql server user defined functions available.

1. Scalar Function
2. Table-Valued Function

SQL server scalar function

Sql server scalar function returns a single value and the data type of return value should be the same as return type. The return type can be any data type except text, ntext, image, cursor, and timestamp.

sql server scalar function Example

CREATE FUNCTION DBO.IS_TOTAL_PRICE_EVEN_OR_ODD(@PRICE  INT)
RETURNS BIT
AS
BEGIN
    DECLARE @TOTAL_PRICE  INT
    SELECT @TOTAL_PRICE  = SUM(P.PRICE) 
    FROM PRODUCT P WHERE P.PRICE >= @PRICE

    IF(@TOTAL_PRICE  % 2 = 0)
        RETURN 1

    RETURN 0
END

SELECT  DBO.IS_TOTAL_PRICE_EVEN_OR_ODD(250)        

Sql server table valued function

Sql server table valued function return a table, that means table valued function return result set of a single SELECT statement.

Sql server table valued function Example

CREATE FUNCTION DBO.GET_PRODUCT_INFO_BY_PRICE(@PRICE  INT)
RETURNS TABLE
AS
RETURN   
( 
    SELECT P.ID, P.NAME, P.PRICE 
    FROM PRODUCT P WHERE P.PRICE >= @PRICE
)
GO
SELECT ID,NAME,PRICE FROM DBO.GET_PRODUCT_INFO_BY_PRICE(250)    

Sql server function return multiple value

Sql server has two types of function one is scalar function and another one is table valued function. Using a scalar function you can not return multiple values, and for a table value function you need to return a table.
So there is no general way to return multiple values but technically you can return multiple values from a function in sql server.

The idea is you need to create a table valued function and from the function you need to return a temporary table which will contain all of your values. Finally when you call your function select the very first row and set the value to your variable.

CREATE FUNCTION [DBO].[GET_LARGE_AND_SMALL](@NUM  INT)
RETURNS @T  TABLE(LARGE INT, SMALL INT)
AS
BEGIN
  DECLARE @LARGE  INT
  DECLARE @SMALL  INT

  SET @LARGE  = @NUM  + 10
  SET @SMALL  = @NUM  - 10

  INSERT INTO @T(LARGE, SMALL) VALUES (@LARGE,@SMALL)
  RETURN
END

DECLARE @LARGENUM  INT
DECLARE @SMALLNUM  INT

SELECT TOP 1 @LARGENUM  = LARGE, @SMALLNUM  = SMALL FROM [DBO].[GET_LARGE_AND_SMALL](100)

PRINT(@LARGENUM) – 110
PRINT(@SMALLNUM) – 90