SQL Server creation functions

In the process of using the database, we often need to calculate some of the data first, and then query it out, so we need to create a function to accomplish this task.

The function you create typically has two return types, one that returns a collection (a data table), and one that returns a string directly.

The following is an example of creating two types of functions

1. Collection

Create a function Fn_selectworktime

Create FUNCTIONFn_selectworktime (@plantID int    , @CheckTime datetime --Attendance Time,@deptID int --Department,@wShop int -- Section,@roleID int --Post,@wGroup int --Team,@uID int --Employees)RETURNS @retValue Table(atworkdatetime, Offworkdatetime) asBEGIN    Declare @onTime datetime    , @offTime datetime        --......    Set @onTime = '2016-07-01'    Set @offTime = '2016-07-01 17:00'    Insert  into @retValue(Atwork, offwork)Values(@onTime,@offTime)    ReturnEND--calling the created function in a stored procedureSelect *  fromDbo.fn_selectworktime (@plantid,@time1,@deptID,@wShop,@roleID,@wGroup,@uid5)

2. String

Create a function Fn_calstdatt

CREATE FUNCTIONFn_calstdatt (@CheckTime datetime --Attendance Time,@deptID int --Department,@wShop int -- Section,@roleID int --Post,@wGroup int --Team,@uID int --Employees,@which Char(1)--I: Working hours, O: Off hours)RETURNS varchar( -) asBEGIN    Declare @retValue datetime    --......    Set @retValue = '2016-07-01'    Return Convert(varchar( -),@retValue, -)END--calling the created function in a stored procedureSelectDbo.fn_calstdatt (@CheckTime,@deptID,@wShop,@roleID,@wGroup,@uID,@which)

SQL Server creation functions