SQL Stored Procedure preventing to write 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

回答1:

I'm still a little confused by your question, but this should work as I understand. Instead of all the IF statements, consider moving into a single CASE statement, defaulting the value to 0. Then add a check if the value isn't 0, to do something.

DECLARE @Level int
DECLARE @Total int
DECLARE @NewPoint int

SET @Level = 61
SET @Total = 100

SELECT 
  @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
                ELSE 0
              END

IF @NewPoint <> 0 
BEGIN
  SELECT 'DO SOMETHING'
END

• SQL Fiddle Demo

回答2:

If I'm understanding this correctly you could modify the ELSE.

Change From:

ELSE @NewPoint = @NewPoint

Change To:

ELSE return

This will abort the script and no updates will occur.

回答3:

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