SQL Server 2008 Computed Column Error With Multiple Inserts

  • I have a table in SQL Server 2008 - let's call the table MyTable. The table has a column named Status, which is not a computed column, and is defined as varchar(40) and it allows NULLs; however, there is also a DEFAULT CONSTRAINT on this column, with the default value = 'POOL'. I just added a computed column to the table, using the following:

    ALTER TABLE MyTable

    ADD PrimaryStatus AS

    CASE

    WHEN Status LIKE '%/%' THEN LEFT(Status,CHARINDEX('/', Status) - 1)

    ELSE Status

    END PERSISTED

    If I insert records into the table one by one (and let Status default to 'POOL' using the constraint) it works just fine; for instance, this SQL statement has no problem:

    INSERT INTO MyTable (Name) VALUES ('Foo')

    With the above SQL, I end up with a new record in the table with Name = 'Foo' and Status = 'POOL' and PrimaryStatus = 'POOL'

    But if I execute a multi-row INSERT like the following:

    INSERT INTO MyTable (Name) VALUES ('Foo'),('Bar')

    then it throws an error:

    Msg 537, Level 16, State 2, Line 1

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    If I drop either the default constraint or the computed column (or both), the multi-row INSERT works fine; but for some reason having both the constraint and the computed column are causing the multi-row INSERT to fail. I have tried tweaking the computed column in a variety of ways to account for NULLs (even though I don't think it should matter given the order of evaluation), but nothing seems to remedy the problem.

    Anybody ever seen something like this before?

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply