November 13, 2007 at 4:23 am
Hi, I have written a query and wish to use the function datename to be used as an alias for a column name. I have written the query in a view and I am not sure how to get round this.
Is there anyway I can use:
convert(nvarchar(15),datename(mm,dateadd(month,-1,getdate())))
as an alias? Or do something similar to this?
Cheers,
Sam
November 13, 2007 at 4:27 am
Hi Sam,
you could use dynamic SQL like:
declare @q varchar(1000)
set @q = 'select 1 as ' + datename(month, getdate())
exec(@q)
Regards,
Andras
November 13, 2007 at 11:04 am
Hi, I have tried that, the problem is that you cannot declare variables in views (not unless you know of a way of doing this?). Do you know of any other ways round this?
Cheers,
Sam
November 14, 2007 at 1:50 am
Sam (11/13/2007)
Hi, I have tried that, the problem is that you cannot declare variables in views (not unless you know of a way of doing this?). Do you know of any other ways round this?Cheers,
Sam
Hi Sam,
the columns of views cannot be named dynamically, i.e. you need to recreate the view every time (or use * in the views, and change the underlying tables, then do a sp_refreshview). You could get around this limitation if you create a stored procedure that returns the required resultset. However, there is a good reason for having fixed names for view columns. Clearly in applications and other SQL queries one would access the columns by their names (using column sequence number is bad). So I'm wondering what you are trying to achieve. Would you mind sharing it with us?
Regards,
Andras
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply