sqlserver自定义函数
USE [BooksBrothersWebOrder]
GO
/****** Object: UserDefinedFunction [dbo].[fx_GetCHSizeData] Script Date: 07/24/2012 09:15:11 ******/
SET QUOTED_IDENTIFIER ON
GO
/*
Name: dbo.fx_GetCHSizeData
Description: --处理Size 的CH数据
Parameters:
@CH VARCHAR(20)
Return Values:
@CONTENT
Ref. Tables:
-------------------------------------------------------------------------------------------------
Author Name:
Creation Date:
Modification Log:
Example:
select * from dbo.fx_GetCHSizeData('BB000574,BB000543')
select * from dbo.fx_GetCHSizeData('170/96B/ 170')
*/
ALTER FUNCTION [dbo].[fx_GetCHSizeData]
(
@RefNoList VARCHAR(100)
)
RETURNS @CHData TABLE
(
CH VARCHAR(20),
CN VARCHAR(10),
CN2 VARCHAR(10)
)
AS
BEGIN
DECLARE @CH VARCHAR(20),@RIndex INT,@RefNo CHAR(8)
SELECT @RefNoList=@RefNoList+','
SELECT @RIndex=CHARINDEX(',',@RefNoList)
WHILE @RIndex>0
BEGIN
--循环读取
SELECT @RefNo=SUBSTRING(@RefNoList,1,@RIndex-1)
SELECT @RefNoList=SUBSTRING(@RefNoList,@RIndex+1,LEN(@RefNoList)-@RIndex)
SELECT @RIndex=CHARINDEX(',',@RefNoList)
DECLARE myCursor CURSOR FOR
SELECT DISTINCT D.CH
FROM dbo.M_SizeDetail D
LEFT JOIN dbo.O_OrderSize S ON D.SizeCode=S.SizeCode AND D.Inseam=S.Inseam AND D.USA=S.USA AND D.SizeType=S.SizeType
WHERE S.RefNo =@RefNo
OPEN myCursor
FETCH NEXT FROM myCursor INTO @CH
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @tmpCH VARCHAR(20),@CH1 VARCHAR(10), @CH2 VARCHAR(10),@Index INT,@CharIndex INT
SELECT @CH1='',@CH2='',@Index=0,@tmpCH=@CH
WHILE CHARINDEX('/',@tmpCH)>0
BEGIN
SELECT @CharIndex=CHARINDEX('/',@tmpCH),@Index=@Index+1
IF @Index<3 SELECT @CH1=@CH1+SUBSTRING(@tmpCH,1,@CharIndex)
IF @Index>2 SELECT @CH2=@CH2+LTRIM(SUBSTRING(@tmpCH,1,@CharIndex))
SELECT @tmpCH=SUBSTRING(@tmpCH,@CHarIndex+1,LEN(@tmpCH)-@CharIndex)
END
IF LEN(@tmpCH)>0
BEGIN
SELECT @Index=@Index+1
IF @Index<3 SELECT @CH1=@CH1+@tmpCH
IF @Index>2 SELECT @CH2=@CH2+LTRIM(@tmpCH)
END
INSERT INTO @CHData SELECT @CH,@CH1,@CH2
FETCH NEXT FROM myCursor INTO @CH
END
CLOSE myCursor --关闭游标
DEALLOCATE myCursor
END
RETURN --@Content
END
