Originally there is some problems in SQL 2000. Correct the error and run through SQL 2000.
/ ************************************************** ******* author: (wleii165@yahoo.com) version: 1.0 created: 20,020,227 modified: function: lowercase to uppercase amount of parameters: n_LowerMoney amount in v_TransType species - 1: directly translate, 0: read IT in Words Output: Capital Amount ************************************************************* ************* / CREATE Procedure DBO.L2U (@n_lowermoney Numeric (15, 2), @ v_transtype int, @ Ret varchar (200) Output AS DECLARE @v_lowerstr varchar (200) - - Small write amount declare @v_upperpart varchar (200) Declare @v_upperstr varchar (200) - Cardship amount Declare @i_i int
Set nocount on
SELECT @v_lowerstr = LTRIM (RTRIM (@ n_lowermoney, 20, 2))) - Since the top 20 into the specified accuracy and delete the data left and right
SELECT @i_i = 1select @v_upperstr = ''
While (@i_i <= len (@v_lowerstr)) Begin Select @v_upperpart = Case Substring (@v_lowerstr) - @i_i 1, 1) when '.' Ten 'Yuan' When '0' Then ' Zero 'When' 1 'Then' 壹 'When' 2 'Ten' 'When' 3 'Ten' 'When' 4 'Ten' 'When' 5 'Ten' Wu 'When' 6 'Ten' Lu ' WHEN '7' TEN '柒' WHEN '8' TEN '捌' When '9' Ten '' end case @i_i when 1 Then 'points' When 2 Ten' angle 'When 3 Then' 'WHEN 4 THEN' 'When 5 TEN' Picking 'WHEN 6 THEN 'WHEN 7 TEN' WHEN 8 TEN 'WHEN' WHEN 9 THEN 'Picking' WHEN 10 TEN 'WHEN' WHEN 11 TEN '仟' WHEN 12 TEN 'Y' WHEN 13 TEN 'Picking' WHEN 14 THEN ' '
When 15 Then '仟' When 16 Then 'Wan' Else '' endselect @v_upperstr = @v_upperpart @v_uppersTRSELECT @i_i = @i_i 1END -------- Print '// v_upperstr =' @ v_upperstr '//'
if (@ v_TransType = 0) beginselect @v_UpperStr = REPLACE (@v_UpperStr, 'zero pick up', 'zero') select @v_UpperStr = REPLACE (@v_UpperStr, 'zero Bai', 'zero') select @v_UpperStr = REPLACE (@ v_upperstr, 'zero', 'zero') select @v_upperstr = Replace (@v_upperstr, 'zero zero', 'zero') select @v_upperstr = Replace (@v_upperstr, 'zero', 'zero') SELECT @ v_upperstr = Replace (@v_upperstr, 'zero angle zero ",' whole ') select @v_upperstr = Replace (@v_upperstr,' zero", 'whole') select @v_upperstr = Replace (@v_upperstr, 'zero angle', 'Zero') select @v_upperstr = Replace (@v_upperstr, '亿 零 元', 'billion ") SELECT @v_upperstr = Replace (@v_upperstr,' billion yuan", 'billion') SELECT @v_upperstr = Replace (@v_upperstr, '亿', 'billion') select @v_upperstr = Replace (@v_upperstr, '零 元', '10,000 yuan') select @v_upperstr = Replace (@v_upperstr, '万 零 元 ',' 10,000 yuan ') select @v_upperstr = Replace (@v_upperstr,' 亿 ',' billion ') select @v_upperstr = Replace (@v_upperstr,' 万 ',' 万 ') Select @v_upperstr = Replace (@v_upperstr, 'zero ",' yuan ') select @v_upperstr = Replace (@v_upperstr,' zero zero ',' zero ') End - The amount of the following amount below the amount of the amount below the (Substring (@v_upperstr, 1, 1) = 'yuan') begin select @v_upperstr = Substring (@v_upp ERSTR, 2, (Len (@v_upperstr) - 1))
IF (@ v_upperstr, 1, 1) = 'zero') Begin select @v_upperstr = Substring (@ v_upperstr, 2, (Len (@v_upperstr) - 1)) End
IF (Substring (@ v_upperstr, 1, 1) = 'angle') begin select @v_upperstr = Substring (@ v_upperstr, 2, (@v_upperstr) - 1)) Endif (Substring (@ v_upperstr, 1, 1) = '分') Begin Select @v_upperstr = Substring (@ v_upperstr, 2, (len (@v_upperstr) - 1)) End
IF (Substring (@ v_upperstr, 1, 1) = 'whole') begin select @v_upperstr = 'zero element' end
SELECT @ Ret = @ v_upperstr
Go
Calling process:
Declare @ret varchar (200)
Exec L2U 567983.897, 1, @ Ret Output
SELECT @ret
