SQL Server Computed Column Error With Multiple Inserts

Question

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?

Answer 1

I tried to replicate the error. But, I don't get any error. I could have added this as a comment, but don't have enough points yet. Anyway, this is what i did -

CREATE TABLE [dbo].[MyTable](
    [Name] [varchar](50) NULL,
    [Status] [varchar](50) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_Status]  
DEFAULT ('POOL') FOR [Status]
GO

Then i removed an extra ) in your code and did -

ALTER TABLE MyTable ADD PrimaryStatus AS 
CASE WHEN Status LIKE '%/%' THEN LEFT(Status,CHARINDEX('/',Status)-1) 
ELSE Status END PERSISTED

Followed by -

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

It works. Am I missing something ?