SQL SERVER~创建函数

1. 创建标量函数

USE [iROCK];
GO
/****** Object:  UserDefinedFunction [dbo].[fn_selectWorkTimess]    Script Date: 2021/6/9 13:51:14 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
--/*********************************************************************************************************
--建立者: HERMAN  日期﹕2021-06-09 
--调用的程序﹕
--说明﹕获取工时查秒数
--更新记录﹕
-- 日期                 更改人             更新说明
------------------  -------------          --------------------------------------------
-- 2021-06-09			HERMAN            新增
--**********************************************************************************************************/
GO
CREATE FUNCTION [dbo].[fn_selectWorkTimess] 
(
    @SelectTime DATETIME --查询时间
)
RETURNS INT   
AS   
BEGIN  
        DECLARE @starttime DATETIME 
		DECLARE @Time DATETIME 
		DECLARE @Time_SS INT 
			
        SET @starttime = CONVERT(VARCHAR(10), GETDATE(), 120) + ' 08:00:00.00';						 --每天八点开始

        SET @Time = CAST(CAST(@SelectTime AS FLOAT)- CAST(@starttime AS FLOAT) AS DATETIME);	     --获取时间差

		SET @Time_SS = DATEPART(hh, @Time)*3600+DATEPART(mi, @Time)*60+DATEPART(ss, @Time);	         --计算时间查秒数 					 
			 
    RETURN @Time_SS;  
END;

//调用
SELECT dbo.fn_SelectWorkTimess(GETDATE())AS time
//执行结果
time
-----------
32562

(1 行受影响)

2. 创建表值函数

IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL  
    DROP FUNCTION Sales.ufn_SalesByStore;  
GO  
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)  
RETURNS TABLE  
AS  
RETURN   
(  
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'  
    FROM Production.Product AS P   
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID  
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID  
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID  
    WHERE C.StoreID = @storeid  
    GROUP BY P.ProductID, P.Name  
);
//调用
SELECT * FROM Sales.ufn_SalesByStore (602);