SQLServer 表值函数与标量值函数 定义与调用
标量值函数创建:
Create FUNCTION [dbo].[GetBidDates]
(
@ID INT
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @Names NVARCHAR(4000)
IF EXISTS(SELECT * FROM BiddingSection WHERE BiddingInfoID = @ID HAVING COUNT(*) > 1)
SELECT @Names = COALESCE(@Names + ' ' + '[' + SectionName + ' ' + CONVERT(NVARCHAR(20),BiddingDate,120) + ']',
'[' + SectionName + ' ' + CONVERT(NVARCHAR(20),BiddingDate,120) + ']',@Names)
FROM BiddingSection
WHERE BiddingInfoID = @ID
ELSE
SELECT @Names = CONVERT(VARCHAR,BiddingDate,120)
FROM BiddingSection
WHERE BiddingInfoID = @ID
RETURN @Names
END
表值函数创建:
Create Function [dbo].[GetAllGoods]
其中标量值函数调用的时候方式如下:select dbo.GetBidDates(001)注意:函数前边一定要加上所有者:dbo
表值函数调用方法如下:select * from GetAllGoods()
联系客服