【SQL】使用者自訂函數_純量值函數(SF)

純量值函數(SF)

使用SQL FUNCTION自定函數來進行字串連結

use AdventureWorks2012
GO

IF OBJECT_ID(N’dbo.getCommaString’,N’FN’) IS NOT NULL
DROP FUNCTION dbo.getCommaString
GO

–建立Function
CREATE FUNCTION getCommaString(@SalesOrderID int)
RETURNS varchar(1000)
BEGIN
DECLARE @string varchar(1000)
SET @string = ”

SELECT @string = @string + CAST(ProductID AS VARCHAR(50)) + ','
FROM [Sales].[SalesOrderDetail]
WHERE SalesOrderID = @SalesOrderID
ORDER BY ProductID

SET @string = LEFT(@string,LEN(@string)-1)
RETURN @string

END
GO

–使用自訂函數來進行字串連結
SELECT
SalesOrderID,
dbo.getCommaString(SalesOrderID) AS Lists
FROM [Sales].[SalesOrderHeader]
WHERE CONVERT(VARCHAR,OrderDate,112) = ‘20110531’