Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

User Defined Scalar Function

User Defined Scalar Function:

    User-defined scalar functions return a single value, according to requirement any return type you can use in function.

 The timestamp data type, user-defined data type, and no scalar types, such as table or cursor, are not supported. The body of the function, defined in a BEGIN...END block, contains the series of Transact-SQL statements that return the value. The return type can be any data type except text, ntext, image, cursor, and timestamp.

Example:

-- =============================================

-- Author:        Nagnath Kendre

-- Create date: <Create Date, ,>

-- Description:   User defined function returns full name

-- =============================================

ALTER FUNCTION GetUserFullName

(

      -- Add the parameters for the function here

 @UserFirstName varchar(70),

 @UserLastName varchar(70)

)

RETURNS NVARCHAR(250)

AS

BEGIN RETURN (SELECT @UserFirstName + ' '+ @UserLastName);

END

 

GO

 

Use of Function in select List:



 

Inline Table-Valued Function

Inline Table-Valued Function:

User Defined functions returns table variable as result of actions, logic performed by function.

Function allows only single Select statement enclosed by parentheses, there is no function Body delimited by BEGIN and END. The RETURNS clause contains only the keyword table.

Example:

-- =============================================

-- Author:        Nagnath Kendre

-- Create date: <Create Date,,>

-- Description:   Return total amount spent by user in web shop till date

-- =============================================

CREATE FUNCTION GetTotalSpentByUser_FN

(    

      @LoginName Nvarchar(50)

)

RETURNS TABLE

AS

RETURN

(

-- Add the SELECT statement with parameter references here

SELECT U.LoginName,SUM(O.total) TotalSpent

FROM [order] O INNER JOIN [User] U on O.UserID=U.Id WHERE U.LoginName=@LoginName GROUP BY U.LoginName

)

GO

 

Use of function in From Clause:



Multi-Statement Table-Valued Function

Multi-Statement Table-Valued Function:

A Multi-Statement Table-Valued user-defined function returns a table variable as function result.

It can have one or more than one T-Sql statement.  Within the create function command you must define the table structure that is being returned

Example:

-- =============================================

-- Author:        NAGNATH

-- Create date: <Create Date,,>

-- Description:   Return total amount spent by user in web shop till date as a Table variable @TotalAmountSpentByUser

-- =============================================

CREATE FUNCTION GetAmountSpentByUser_FN

( @LoginName Varchar(50) )

RETURNS

@TotalAmountSpentByUser table (

 NAME VARCHAR(50),

 TotalAmount DECIMAL(20,10)

)

AS

BEGIN

 ---Return result as Table for user

INSERT INTO @TotalAmountSpentByUser

SELECT U.LoginName,SUM(O.total) TotalSpent

FROM [order] O INNER JOIN [User] U on O.UserID=U.Id WHERE U.LoginName=@LoginName GROUP BY U.LoginName

 

 --if no user Found return warning message as name

IF @@ROWCOUNT = 0

BEGIN

INSERT INTO @TotalAmountSpentByUser

VALUES ('User not found',0)

END

 

RETURN

END

GO

 

Use of function in From Clause for existing user:



Not existing users