Is there a way to add variable value for new column in Alter Table statement?

DECLARE @VName      VARCHAR(8)

SET @VNAme = (
                          SELECT TOP 1 xyz 
                          FROM table
                          WHERE abc = something
                    )

ALTER TABLE table_name 
ADD new_column VARCHAR (8)  NOT NULL DEFAULT @VName

Answer

First read this post about SQL Injection. Then you can use Dynamic SQL at your own risk, something like the following:

DECLARE @AlterSQL VARCHAR(MAX) = '
    ALTER TABLE table_name ADD new_column 
        VARCHAR(8) 
        NOT NULL 
        DEFAULT ''' + REPLACE(@VName, '''', '''''') + ''''

EXEC (@AlterSQL)

If you want the expression to change over time, read akhilesh’s function answer.