Bonnie,
Like the others said SQLServer isn't natively that dynamic without making the whole TSql statement dynamic. I'm not sure what is consuming the result but I think it's trivial to rename a column of a DataSet object once it gets down to your .Net code.
>I'd like to parameterize this, but if I try this it gives an incorrect syntax error:
>
>DECLARE @KeyName varchar(50)
>SET @KeyName = 'codesdeptkey'
>SELECT CodesKey AS @KeyName, * from codes