SQL Server中Modulo11函数创建失败及账户号校验约束实现求助

Let's break down the issues you're facing and fix them step by step:

1. Why You're Getting the Msg 8152 Error #

The immediate cause is the status column in your table variables is defined as char(5), but you're trying to insert the string 'not valid' which is 8 characters long—this exceeds the column's length, hence the truncation error. Also, your @total variable isn't initialized (it starts as NULL), so all calculations involving it will stay NULL, making your validation logic useless.

2. Rewriting the Function for CHECK Constraint Use #

Your original function returns a table, which isn't suitable for a CHECK constraint—CHECK constraints require a scalar function that returns a boolean-like value (we'll use bit here, where 1 = valid, 0 = invalid). Here's the corrected scalar version:

CREATE FUNCTION dbo.CheckModulo11(@accountNr BIGINT) -- Use BIGINT to avoid overflow for longer account numbers
RETURNS BIT
AS
BEGIN
    DECLARE @changeNums VARCHAR(20) = CONVERT(VARCHAR(20), @accountNr);
    DECLARE @length INT = LEN(@changeNums);
    DECLARE @counter INT = 1;
    DECLARE @value INT;
    DECLARE @total INT = 0; -- Initialize total to 0 to avoid NULL calculations

    WHILE @length > 0
    BEGIN
        SET @value = CONVERT(INT, SUBSTRING(@changeNums, @length, 1));
        SET @total = @total + (@counter * @value);
        SET @length = @length - 1;
        SET @counter = @counter + 1;
    END

    -- Return 1 if valid, 0 otherwise
    RETURN CASE WHEN @total % 11 = 0 THEN 1 ELSE 0 END;
END
GO

Key Improvements: #

• Changed to a scalar function (returns BIT) which works seamlessly with CHECK constraints
• Initialized @total to 0 to fix NULL calculation issues
• Used BIGINT for @accountNr to handle longer account numbers that might exceed INT limits
• Removed unnecessary table output since we only need a validity flag for the constraint

3. Creating a Table with the CHECK Constraint #

Now let's create a table that stores account numbers and uses our function to enforce validity:

CREATE TABLE CustomerAccounts (
    AccountID INT IDENTITY(1,1) PRIMARY KEY,
    AccountNr BIGINT NOT NULL,
    -- Add the CHECK constraint using our validation function
    CONSTRAINT CK_AccountNr_Modulo11 CHECK (dbo.CheckModulo11(AccountNr) = 1)
);
GO

4. Testing the Constraint #

Let's test inserting valid and invalid account numbers to verify the constraint works:

Valid Account (should succeed) #

-- First verify your test number is valid
SELECT dbo.CheckModulo11(972428577) AS IsValid; -- Returns 1 (valid)

INSERT INTO CustomerAccounts (AccountNr) VALUES (972428577);
-- This runs without errors

Invalid Account (should fail) #

INSERT INTO CustomerAccounts (AccountNr) VALUES (123456789);
-- You'll get an error: The INSERT statement conflicted with the CHECK constraint "CK_AccountNr_Modulo11".

If You Still Need the Table-Valued Function for Debugging #

If you want to keep the table output to see the calculation steps, fix the status column length and initialize @total:

CREATE FUNCTION dbo.CheckModulo11_Debug(@accountNr BIGINT) 
RETURNS @tableModulo11 TABLE( 
    modulo_nr INT, 
    number INT, 
    total INT, 
    status VARCHAR(10) -- Increased length to fit 'not valid'
) 
AS 
BEGIN 
    DECLARE @tmpTable TABLE( 
        modulo_nr INT, 
        number INT, 
        total INT, 
        status VARCHAR(10)
    );
    DECLARE @length INT, @counter INT = 1, @value INT, @total INT = 0, @changeNums VARCHAR(20);
    
    SET @changeNums = CONVERT(VARCHAR(20), @accountNr);
    SET @length = LEN(@changeNums);
    
    WHILE @length > 0 
    BEGIN 
        SET @value = CONVERT(INT,SUBSTRING(@changeNums, @length, 1));
        INSERT INTO @tmpTable (modulo_nr, number, total, status) 
        VALUES (@counter, @value, (@counter * @value), '-');
        
        SET @total = @total + (@counter * @value);
        SET @length = @length - 1;
        SET @counter = @counter + 1;
    END 
    
    IF @total % 11 = 0
        INSERT INTO @tmpTable (modulo_nr, number, total, status) 
        VALUES (NULL, NULL, @total, 'valid');
    ELSE
        INSERT INTO @tmpTable (modulo_nr, number, total, status) 
        VALUES (NULL, NULL, @total, 'not valid');
    
    INSERT INTO @tableModulo11 SELECT * FROM @tmpTable;
    RETURN;
END 
GO

-- Test the debug function
SELECT * FROM dbo.CheckModulo11_Debug(972428577);

内容的提问来源于stack exchange，提问作者Abdullah Ubaid