存储过程 - TRY_CAST() [英] Stored Procedure - TRY_CAST()

问题描述

我做了一个使用 TRY_CAST(@VAR as decimal(18,2)) 的查询它正在工作，但是当我尝试使用0.00"时，它返回空

I made a query that uses TRY_CAST(@VAR as decimal(18,2)) it is working but when I tried on '0.00', it returns null

DECLARE @FROMHEADERPH TABLE (id int identity, headername nvarchar(max))
INSERT INTO @FROMHEADERPH
SELECT LTRIM(RTRIM(column_name)) FROM tbl_hrms_header where item_group = 'PerHour'

DECLARE @PERHOUR int
SELECT @PERHOUR = COUNT(*) from @FROMHEADERPH
DECLARE @ALLDATEFORMATSBM TABLE (
[Id]        int identity,
[SBM]   nvarchar(max)
)

DECLARE @MAX int = 1
WHILE @MAX <= @PERHOUR
BEGIN
    DECLARE @WEWE nvarchar(max)
    SELECT @WEWE = headername from @FROMHEADERPH where id = @MAX


    DECLARE @q nvarchar(max)
    SET @q = N'SELECT '+ @WEWE +' FROM TemporaryProfile'
    INSERT INTO  @ALLDATEFORMATSBM(SBM)
    EXEC (@q)

    DECLARE @IFCONVERTED table(id int identity, [FIELDS] nvarchar(max));

    DECLARE @TSBM table(id int identity, [TSBM] nvarchar(max)); 
    DECLARE @VSBM nvarchar(max);

    DECLARE @MAXSBM int
    SELECT @MAXSBM = MAX(id) FROM @ALLDATEFORMATSBM
    DECLARE @COUNTSBM int;
    SELECT @COUNTSBM = MIN(id) FROM @ALLDATEFORMATSBM
    WHILE @MAXSBM >= @COUNTSBM
    BEGIN
        DECLARE @SBM nvarchar(max);
        SELECT @SBM = SBM FROM @ALLDATEFORMATSBM WHERE Id = @MAXSBM


        INSERT INTO @TSBM
        SELECT   
        CASE WHEN TRY_CAST(@SBM AS decimal(18,2)) IS NULL   
        THEN 'Cast failed'  
        ELSE 'Cast succeeded'  
        END AS Result;

        DECLARE @WEW int
        SELECT @WEW = MIN(id) FROM @TSBM;


            SELECT @VSBM = TSBM FROM @TSBM Where id = @WEW
        PRINT CAST(@MAXSBM as nvarchar(max)) + ' - ' + CAST(@COUNTSBM as nvarchar(max)) + ' # ' + @SBM + ' !@ ' + CAST(@WEW as nvarchar(max)) + ' :: ' + @VSBM
            if @VSBM = 'Cast failed'
            BEGIN
             insert into @IFCONVERTED([FIELDS]) values(@WEWE);
             BREAK;
            END
            ELSE
            BEGIN
             Print '---'
            END


        DELETE FROM @TSBM
        SET @MAXSBM = @MAXSBM - 1
    END
    DELETE FROM @ALLDATEFORMATSBM
    SET @MAX = @MAX + 1
END
SELECT * FROM @IFCONVERTED

@FROMHEADERPH 有:

@FROMHEADERPH has:

dailyPay | MonthLyPay | YearlyPay
Leona    | 0.00       | 0.00
Elizabeth| 0.00       | 0.00

我用字符串填充了 dailypay 来检查我的 try_cast.它在第一列工作，但当其他两列有数值时，它无法转换.

I made dailypay populated with string to check my try_cast. It is working at first column but when it comes two other columns that have numerical values, it fails casting.

有人可以帮我吗?我真的已经筋疲力尽了.:(

can anyone help me here? Im really burned out already. :(

推荐答案

我重构了我的查询.这是我的最终查询

I reconstructed my query. Here is my final query

DECLARE @FROMHEADERPH TABLE (id int identity, headername nvarchar(max))
INSERT INTO @FROMHEADERPH
SELECT LTRIM(RTRIM(column_name)) FROM tbl_hrms_header where item_group = 'PerHour'

DECLARE @IFCONVERTED table(id int identity, [FIELDS] nvarchar(max)); --  collect all invalid fields

DECLARE @PERHOUR int
SELECT @PERHOUR = COUNT(*) from @FROMHEADERPH
DECLARE @ALLDATEFORMATSBM TABLE (
    [Id]        int identity,
    [DataStringss]   nvarchar(max)
    )
DECLARE @MAX int = 1
WHILE @MAX <= @PERHOUR
BEGIN
    DECLARE @WEWE nvarchar(max)
    SELECT @WEWE = headername from @FROMHEADERPH where id = @MAX


    --PRINT @WEWE + '-' + cast(@MAX AS NVARCHAR(MAX))
    DECLARE @q nvarchar(max)
    SET @q = N'SELECT '+ @WEWE +' FROM TemporaryProfile'
    INSERT INTO  @ALLDATEFORMATSBM([DataStringss])
    EXEC (@q)
    SELECT * FROM @ALLDATEFORMATSBM

    DECLARE @COUNTH int, @MAXH int = (SELECT MIN(Id) FROM @ALLDATEFORMATSBM)
    SELECT @COUNTH = MAX(Id) FROM @ALLDATEFORMATSBM
    PRINT CAST(@MAXH as nvarchar(max)) + '-' + CAST(@COUNTH as nvarchar(max))

    WHILE @MAXH <= @COUNTH
    BEGIN
        DECLARE @DS nvarchar(max)
        SELECT @DS = [DataStringss] FROM @ALLDATEFORMATSBM WHERE Id = @MAXH
        PRINT @DS

        DECLARE @CASTING nvarchar(max)
        SELECT @CASTING = TRY_CAST(@DS AS NUMERIC(18,2))

        IF @CASTING is null
        BEGIN
            INSERT INTO @IFCONVERTED
            VALUES(@WEWE)
            BREAK
        END
        ELSE
        BEGIN
            PRINT '----'
        END

        SET @MAXH = @MAXH + 1
    END


    DELETE FROM @ALLDATEFORMATSBM
    SET @MAX = @MAX + 1
END
    SELECT DISTINCT * FROM @IFCONVERTED

这篇关于存储过程 - TRY_CAST()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！