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