USE [BooksBrothersWebOrder]
GO
/****** Object: UserDefinedFunction [dbo].[fx_GetMultiLineContentByRefNo] Script Date: 07/24/2012 08:47:18 ******/
SET QUOTED_IDENTIFIER ON
GO
/*
Name: dbo.fx_GetContentByRefNoWithMultiLine
Description: --根据RefNo取得Content资料
Parameters:
@RefNo char(8) 订单编号
Return Values:
@CONTENT
Ref. Tables:
R dbo.O_OrderContent
-------------------------------------------------------------------------------------------------
Author Name:
Creation Date:
Modification Log:
Example:
select dbo.fx_GetContentByRefNoWithMultiLine('BB000193')
select * from dbo.fx_GetMultiLineContentByRefNo('BB000005', 'English')
select dbo.fx_GetContentByRefNo('BB000193')
select * from O_OrderContent where refno='BB000193'
select * from M_Type where English='FEL'
*/
ALTER FUNCTION [dbo].[fx_GetMultiLineContentByRefNo]
(
@RefNo CHAR(8),
@Language CHAR(8)
)
RETURNS @ContentLanguage TABLE
(
Language NVARCHAR(20),
Type NVARCHAR(50),
Content NVARCHAR(1000)
)
AS
BEGIN
DECLARE @tmpType AS NVARCHAR(50)
DECLARE @Type AS NVARCHAR(50)
DECLARE @Content AS NVARCHAR(60)
--DECLARE @MaxKorea INT,@MaxJapan INT,@MaxTW INT,@MaxCN INT,@MaxMulti INT
--SELECT @MaxKorea=18,@MaxJapan=19,@MaxTW=13,@MaxCN=13,@MaxMulti=35
--DECLARE @ContentList AS VARCHAR(1000)
--DECLARE @SQL AS VARCHAR(1000)
--SELECT @tmpType='NULL',@Type='',@Content='',@ContentList=''
if @Language = 'English'
BEGIN
DECLARE myCursor CURSOR FOR
SELECT Type= T.English ,
Content= Case WHEN O.Percentage <= '0' Then '' Else O.Percentage +'% ' End + C.English
FROM dbo.O_OrderContent O
LEFT JOIN dbo.M_Type T ON T.Type=O.Type
LEFT JOIN dbo.M_Content C ON C.ContCode=O.ContentCode
LEFT JOIN dbo.O_OrderMaster M ON M.RefNo=O.RefNo
WHERE O.RefNo=@RefNo
ORDER BY O.TypeSequence,O.ContSequence
END
ELSE if @Language = 'Other'
BEGIN
DECLARE myCursor CURSOR FOR
SELECT N'Type'= T.English + '/' + T.French + '/' + T.German + '/' + T.Italian + '/' + T.Spanish + '/' + T.Greek ,
Content=Case WHEN O.Percentage <= 0 Then '' Else O.Percentage +'% ' End + C.English + '/' + C.French + '/' + C.German + '/' + C.Italian + '/' + C.Spanish + '/' + C.Greek
FROM dbo.O_OrderContent O
LEFT JOIN dbo.M_Type T ON T.Type=O.Type
LEFT JOIN dbo.M_Content C ON C.ContCode=O.ContentCode
LEFT JOIN dbo.O_OrderMaster M ON M.RefNo=O.RefNo
WHERE O.RefNo=@RefNo
ORDER BY O.TypeSequence,O.ContSequence
END
ELSE if @Language = 'Korean'
BEGIN
DECLARE myCursor CURSOR FOR
SELECT Type= T.Korean ,
Content= Case WHEN O.Percentage <= 0 Then '' Else O.Percentage +'% ' End + C.Korean
FROM dbo.O_OrderContent O
LEFT JOIN dbo.M_Type T ON T.Type=O.Type
LEFT JOIN dbo.M_Content C ON C.ContCode=O.ContentCode
LEFT JOIN dbo.O_OrderMaster M ON M.RefNo=O.RefNo
WHERE O.RefNo=@RefNo
ORDER BY O.TypeSequence,O.ContSequence
END
ELSE if @Language = 'Japanese'
BEGIN
DECLARE myCursor CURSOR FOR
SELECT Type= T.Japanese ,
Content= Case WHEN O.Percentage <= 0 Then '' Else O.Percentage +'% ' End + C.Japanese
FROM dbo.O_OrderContent O
LEFT JOIN dbo.M_Type T ON T.Type=O.Type
LEFT JOIN dbo.M_Content C ON C.ContCode=O.ContentCode
LEFT JOIN dbo.O_OrderMaster M ON M.RefNo=O.RefNo
WHERE O.RefNo=@RefNo
ORDER BY O.TypeSequence,O.ContSequence
END
ELSE if @Language = 'Chinese'
BEGIN
DECLARE myCursor CURSOR FOR
SELECT Type= T.Chinese ,
Content= Case WHEN O.Percentage <= 0 Then '' Else O.Percentage +'% ' End + C.Chinese
FROM dbo.O_OrderContent O
LEFT JOIN dbo.M_Type T ON T.Type=O.Type
LEFT JOIN dbo.M_Content C ON C.ContCode=O.ContentCode
LEFT JOIN dbo.O_OrderMaster M ON M.RefNo=O.RefNo
WHERE O.RefNo=@RefNo
ORDER BY O.TypeSequence,O.ContSequence
END
ELSE if @Language = 'Taiwan'
BEGIN
DECLARE myCursor CURSOR FOR
SELECT Type= T.Taiwan ,
Content= Case WHEN O.Percentage <= 0 Then '' Else O.Percentage +'% ' End + C.Taiwan
FROM dbo.O_OrderContent O
LEFT JOIN dbo.M_Type T ON T.Type=O.Type
LEFT JOIN dbo.M_Content C ON C.ContCode=O.ContentCode
LEFT JOIN dbo.O_OrderMaster M ON M.RefNo=O.RefNo
WHERE O.RefNo=@RefNo
ORDER BY O.TypeSequence,O.ContSequence
END
OPEN myCursor --开启游标
FETCH NEXT FROM myCursor INTO @Type,@Content
WHILE @@FETCH_STATUS=0
BEGIN
--IF @tmpType<>@Type
--BEGIN
-- IF @tmpType='NULL'
-- SELECT @ContentList= @Type+'+'+@Content+';'
-- ELSE
-- BEGIN
-- --去掉最后的符号
-- IF LEN(@ContentList)>0
-- SELECT @ContentList=LEFT(@ContentList,LEN(@ContentList)-1)
-- SELECT @ContentList=@ContentList+ 'chr(13)' + @Type+'+'+@Content+';'
-- END
--END
--ELSE
--BEGIN
-- SELECT @ContentList=@ContentList+@Content+';'
--END
IF(@tmpType<>@Type) or (@tmpType is null)
INSERT INTO @ContentLanguage SELECT @Language,@Type,@Content
ELSE
BEGIN
INSERT INTO @ContentLanguage SELECT @Language,'',@Content
END
SELECT @tmpType=@Type
FETCH NEXT FROM myCursor INTO @Type,@Content
END
CLOSE myCursor --关闭游标
DEALLOCATE myCursor
----去掉最后的符号
--IF LEN(@ContentList)>0
--SELECT @ContentList=LEFT(@ContentList,LEN(@ContentList)-1)
RETURN -- @ContentLanguage
END