SQL 存储过程阻止写入空值 [英] SQL Stored Procedure preventing to write null

问题描述

所以，我在我的一个存储过程中实现了下面的这个.如果他们指定的点数较少，它可以正常工作，但如果他们有正常的点数(不少于)并且它们是例如级别 61 并且总共有 482 点，那么脚本将尝试在总点数上写入 NULL，我想做到这一点，所以它不会这样做，如果他们对积分没意见就什么都不做.(就像脚本从未执行过一样)因为如果它们有足够的点数，那么我们就不必添加它们.

So, I've got this below implemented in one of my stored procedures. And if they have less points as specified it works correctly but if they have normal points (not less) and they are for example level 61 and with 482 points total then the script will try to write NULL on total Points and I want to make it, so it wont do it and if they are okay with points just to do nothing. (like the script has never been executed) because if they have enough points then we dont have to add them.

GO
/****** Object:  StoredProcedure [dbo].[REPAIR_USER_STAT_POINTS]    Script Date: 05/30/2013 03:57:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[REPAIR_USER_STAT_POINTS]

@strUserID varchar(21)

AS

DECLARE @Level int, @Point int, @Class int,@NewPoint int, @Strong int, @Sta int, @Dex int, @Intel int, @Cha int, @Total int



SELECT @Level = Level, @Class = Class, @Point = Points, @Strong = Strong, @Sta = Sta, @Dex = Dex, @Intel = Intel, @Cha = Cha FROM USERDATA WHERE strUserId = @strUserID

SET @Total = SUM(@Strong + @Sta + @Dex + @Intel + @Cha + @Point)


IF @Level = 61 AND @Total < 482

BEGIN

SET @NewPoint = 10

END

IF @Level = 62 AND @Total < 487

BEGIN

SET @NewPoint = 15

END

IF @Level = 63 AND @Total < 492

BEGIN

SET @NewPoint = 20

END

IF @Level = 64 AND @Total < 497

BEGIN

SET @NewPoint = 25

END

IF @Level = 65 AND @Total < 502

BEGIN

SET @NewPoint = 30

END

IF @Level = 66 AND @Total < 507

BEGIN

SET @NewPoint = 35

END

IF @Level = 67 AND @Total < 512

BEGIN

SET @NewPoint = 40

END

IF @Level = 68 AND @Total < 517

BEGIN

SET @NewPoint = 45

END

IF @Level = 69 AND @Total < 522

BEGIN

SET @NewPoint = 50

END

IF @Level = 70 AND @Total < 527

BEGIN

SET @NewPoint = 55

END

IF @Level = 71 AND @Total < 532

BEGIN

SET @NewPoint = 60

END

IF @Level = 72 AND @Total < 537

BEGIN

SET @NewPoint = 65

END

IF @Level = 73 AND @Total < 542

BEGIN

SET @NewPoint = 70

END

IF @Level = 74 AND @Total < 547

BEGIN

SET @NewPoint = 75

END

IF @Level = 75 AND @Total < 552

BEGIN

SET @NewPoint = 80

END

IF @Level = 76 AND @Total < 557

BEGIN

SET @NewPoint = 85

END

IF @Level = 77 AND @Total < 562

BEGIN

SET @NewPoint = 90

END

IF @Level = 78 AND @Total < 567

BEGIN

SET @NewPoint = 95

END

IF @Level = 79 AND @Total < 572

BEGIN

SET @NewPoint = 100

END

IF @Level = 80 AND @Total < 577

BEGIN

SET @NewPoint = 105

END

IF @Level = 81 AND @Total < 582

BEGIN

SET @NewPoint = 110

END

IF @Level = 82 AND @Total < 587

BEGIN

SET @NewPoint = 115

END

IF @Level = 83 AND @Total < 592

BEGIN

SET @NewPoint = 120

END



IF @Class = 201 OR @Class = 205 OR @Class = 206 OR @Class = 101 OR @Class = 105 OR @Class = 106

BEGIN

UPDATE USERDATA Set Strong = '247',Dex = '60',Sta = '65',Intel = '50',Cha = '50',Points = @NewPoint WHERE strUserId = @strUserId

END

ELSE IF @Class = 202 OR @Class = 207 OR @Class = 208 OR @Class = 102 OR @Class = 107 OR @Class = 108

BEGIN

UPDATE USERDATA Set Strong = '60',Dex = '252',Sta = '60',Intel = '50',Cha = '50',Points = @NewPoint WHERE strUserId = @strUserId

END

ELSE IF @Class = 203 OR @Class = 209 OR @Class = 210 OR @Class = 103 OR @Class = 109 OR @Class = 110

BEGIN

UPDATE USERDATA Set Strong = '50',Dex = '70',Sta = '50',Intel = '70',Cha = '232',Points = @NewPoint WHERE strUserId = @strUserId

END

ELSE IF @Class = 204 OR @Class = 211 OR @Class = 212 OR @Class = 104 OR @Class = 111 OR @Class = 112

BEGIN

UPDATE USERDATA Set Strong = '65',Dex = '60',Sta = '65',Intel = '232',Cha = '50',Points = @NewPoint WHERE strUserId = @strUserId

END

推荐答案

切换到带有 ELSE 子句的 CASE 语句.无论如何它更简洁.

Switch to a CASE statement with an ELSE clause. It's more concise anyway.

GO
/****** Object:  StoredProcedure [dbo].[REPAIR_USER_STAT_POINTS]    Script Date: 05/30/2013 03:57:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[REPAIR_USER_STAT_POINTS]
@strUserID varchar(21)
AS
DECLARE @Level int, @Point int, @Class int,@NewPoint int, @Strong int, @Sta int, @Dex int, @Intel int, @Cha int, @Total int

SELECT @Level = Level, @Class = Class, @Point = Points, @Strong = Strong, @Sta = Sta, @Dex = Dex, @Intel = Intel, @Cha = Cha 
FROM USERDATA WHERE strUserId = @strUserID

SET @Total = SUM(@Strong + @Sta + @Dex + @Intel + @Cha + @Point)


SET @NewPoint = 
    CASE WHEN @Level = 61 AND @Total < 482 THEN 10
        WHEN @Level = 62 AND @Total < 487 THEN 15
        WHEN @Level = 63 AND @Total < 492 THEN 20
        WHEN @Level = 64 AND @Total < 497 THEN 25
        WHEN @Level = 65 AND @Total < 502 THEN 30
        WHEN @Level = 66 AND @Total < 507 THEN 35
        WHEN @Level = 67 AND @Total < 512 THEN 40
        WHEN @Level = 68 AND @Total < 517 THEN 45
        WHEN @Level = 69 AND @Total < 522 THEN 50
        WHEN @Level = 70 AND @Total < 527 THEN 55
        WHEN @Level = 71 AND @Total < 532 THEN 60
        WHEN @Level = 72 AND @Total < 537 THEN 65
        WHEN @Level = 73 AND @Total < 542 THEN 70
        WHEN @Level = 74 AND @Total < 547 THEN 75
        WHEN @Level = 75 AND @Total < 552 THEN 80
        WHEN @Level = 76 AND @Total < 557 THEN 85
        WHEN @Level = 77 AND @Total < 562 THEN 90
        WHEN @Level = 78 AND @Total < 567 THEN 95
        WHEN @Level = 79 AND @Total < 572 THEN 100
        WHEN @Level = 80 AND @Total < 577 THEN 105
        WHEN @Level = 81 AND @Total < 582 THEN 110
        WHEN @Level = 82 AND @Total < 587 THEN 115
        WHEN @Level = 83 AND @Total < 592 THEN 120
        ELSE @Point
    END


IF @Point <> @NewPoint
BEGIN
    IF @Class = 201 OR @Class = 205 OR @Class = 206 OR @Class = 101 OR @Class = 105 OR @Class = 106
    BEGIN
        UPDATE USERDATA Set Strong = '247',Dex = '60',Sta = '65',Intel = '50',Cha = '50',Points = @NewPoint 
        WHERE strUserId = @strUserId
    END
    ELSE IF @Class = 202 OR @Class = 207 OR @Class = 208 OR @Class = 102 OR @Class = 107 OR @Class = 108
    BEGIN
        UPDATE USERDATA Set Strong = '60',Dex = '252',Sta = '60',Intel = '50',Cha = '50',Points = @NewPoint 
        WHERE strUserId = @strUserId
    END
    ELSE IF @Class = 203 OR @Class = 209 OR @Class = 210 OR @Class = 103 OR @Class = 109 OR @Class = 110
    BEGIN
        UPDATE USERDATA Set Strong = '50',Dex = '70',Sta = '50',Intel = '70',Cha = '232',Points = @NewPoint 
        WHERE strUserId = @strUserId
    END
    ELSE IF @Class = 204 OR @Class = 211 OR @Class = 212 OR @Class = 104 OR @Class = 111 OR @Class = 112
    BEGIN
        UPDATE USERDATA Set Strong = '65',Dex = '60',Sta = '65',Intel = '232',Cha = '50',Points = @NewPoint 
        WHERE strUserId = @strUserId
    END
END

这篇关于SQL 存储过程阻止写入空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！