带条件插入的存储过程

medivh 发表于 Dev

麦迪文

我试图用一个If语句编写一个过程。

这是我的数据库

我的想法是：当我们拥有相同时ComandmentNumber，StudentNumber必须加1，但是当我们拥有相同时ComandmentNumber，StudentNumber必须从1开始。

例：

这是我在SQL Server中的过程

CREATE PROCEDURE InsertIntoDefeatsGraduates
    @ComandmentNumber VARCHAR(100),
    @StudentFakNumber INT
AS
BEGIN
    DECLARE @StudentNumber INT 

    IF EXISTS (SELECT * FROM dbo.[Defeats-Graduates] 
               WHERE ComandmentNumber = @ComandmentNumber) 
    BEGIN
        SET @StudentNumber += 1

        INSERT INTO dbo.[Defeats-Graduates] (ComandmentNumber, StudentNumber, StudentFakNumber) 
        VALUES (@ComandmentNumber, @StudentNumber, @StudentFakNumber)
    END
    ELSE
    BEGIN
        SET @StudentNumber = 1

        INSERT INTO dbo.[Defeats-Graduates](ComandmentNumber, StudentNumber, StudentFakNumber) 
        VALUES (@ComandmentNumber, @StudentNumber, @StudentFakNumber)
    END
END

但是当我尝试执行此操作时，出现以下错误：

无法将值NULL插入表'Test.dbo.Defeats-Graduates'的'StudentNumber'列中；列不允许为空。INSERT失败。

肖恩·肖

@StudentNumber在第一种情况下，您没有任何赋值语句，可以尝试以下操作：

CREATE PROCEDURE InsertIntoDefeatsGraduates
    @ComandmentNumber VARCHAR(100),
    @StudentFakNumber INT
AS
BEGIN
    DECLARE @StudentNumber INT 
    SELECT @StudentNumber = StudentNumber  FROM dbo.[Defeats-Graduates] 
               WHERE ComandmentNumber = @ComandmentNumber
    IF ISNULL(@StudentNumber, 0) > 0
      BEGIN
        SET @StudentNumber += 1

        INSERT INTO dbo.[Defeats-Graduates] (ComandmentNumber, StudentNumber, StudentFakNumber) 
        VALUES (@ComandmentNumber, @StudentNumber, @StudentFakNumber)
      END
    ELSE
      BEGIN
        SET @StudentNumber = 1

        INSERT INTO dbo.[Defeats-Graduates](ComandmentNumber, StudentNumber, StudentFakNumber) 
        VALUES (@ComandmentNumber, @StudentNumber, @StudentFakNumber)
      END
    END

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。