You can use a dynamic query.

Try something like this:

create procedure sp_test

@datefield varchar(20)

as

set nocount on

declare @queryString varchar(200)

set @queryString = 'select '+datename(dw,@datefield)+' = ''a value'''

execute(@queryString)

execute sp_test '07/19/04'

execute sp_test '07/20/04'

execute sp_test '07/21/04'

You should get the alias 'Monday', 'Tuesday' & 'Wednesday' (with 'a value' as the value)