How to add not null column in SQL Server 2005

[bhavna_816]

I have to add a not null column in a table.
I am using

IF NOT EXISTS(SELECT * FROM information_schema.columns WHERE column_name = 'xyz' 
AND table_name = 'abc') 	
BEGIN
	ALTER TABLE abc	WITH NOCHECK ADD xyz MONEY NOT NULL
END

It gives me error
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'xyz' cannot be added to non-empty table 'abc' because it does not satisfy these conditions.

can anybody have any idea, what's wrong with my SP?

?

[Ramy Mahrous]

You can't, simply when you add a column all rows before this adding have this column = null, so you can remove all rows and add a non-nullable column.

[plazmo]

if you want to add a not null column to a table with rows, you need to set a default value to the new column.

adding a new column without a default value would put a null in the column for each row, which would be breaking the not null rule.

[Saranya_kamaraj]

U cant add a not null column using ALTER command.Because it allows
to add only null columns.If u want to add datas into that null column you need to use UPDATE command for adding data into that table
Like,

UPDATE TABLE table_name SET null_column-name='value' WHERE column_name='value

EX
UPDATE TABLE employee SET add1='SARA' WHERE sno=1