Using a CAST(NULL as <DataType>) is usually done to ensure that a place holder column has the right datatype for a later process especially when doing an SELECT INTO or when doing a Union across 2 tables with different columns, e.g.

Select

Column1

,column2

,CAST(NULL as Varchar(50)) as Column3

From

Table1

UNION ALL

Select

Column1

,column2

,Column3

Into #tmp

From

Table2

The other common use is when you are working with RS and SSIS calling SP's that use Temp tables, as these applications have problems 'guessing' the data set so you can fool it by doing this at the top of the proc.

IF 1=0

Begin

Select

Cast(NULL as Int) as Column1

Cast(NULL as Decimal(18,2)) as Column2

Cast(NULL as DateTime) as Column3

--- More columns

End