data type change to bit after alter column

Question

I code this query, but unfortunately, I got this error:

"Argument data type bit is invalid for argument 1 of substring function."

My Stored Procedure is:

ALTER TABLE A ALTER COLUMN B nvarchar(20);  
UPDATE A SET B = CASE WHEN SUBSTRING(B, 1, 2)>1000 
        THEN '1' + B ELSE B END

I convert my data but again get an error.

Absolutely my update query work if there is not the first one(Alter Table Alter Column).

Can anyone explaine why this occurred and how should I fix it?

Answer 1

This code works:

create table a (b bit);

alter table a alter column b nvarchar(20);
go

update a
    set b = substring(b, 1, 2);

This code does not:

create procedure p as
begin
    alter table a alter column b nvarchar(20);

    update a
        set b = substring(b, 1, 2);
end;

Why not? The first code actually changes the table, so the update is correct when it is run. Within the stored procedure, the two statements are compiled -- but not run. That means that the update is compiled, but the table has not changed. Hence you get an error.

Although you could solve this using dynamic SQL, I would instead ask why you are modifying column types in a stored procedure. That seems quite irregular. Normally, stored procedures do not do such modifications.