Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tool
Starting Member
26 Posts |
 Posted - 2003-10-14 : 14:33:52
|
What I would like to do is dynamically assign an alias to a column in my query. Something like this:DECLARE @Table table (Column1 varchar(20))DECLARE @Alias varchar(20)SET @Alias = 'Column One'INSERT INTO @Table VALUES ('test')SELECT Column1 AS @Alias FROM @TableObviously this does not work, or I would not be posting. Are there any workarounds or clever solutions for this problem?Thanks |
|
|
bjornh
Yak Posting Veteran
87 Posts |
Posted - 2003-10-14 : 15:23:48
|
| you have to use dynamic sql, iam typing this on the fly, so maybe it has some small errors:exec('select column1 as ' + @alias + ' from ' + @table)if you search @ sqlteam you will find various articles about dynamic sql.Bjorn |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-14 : 15:41:11
|
| Why would you want to do this though? If you dynamic sql, your query isn't going to run as efficiently as it would without the dynamic sql. Security is also a problem with dynamic sql. If your code is inside a stored procedure, it is not sufficient to just add EXEC on the stored procedure. With dynamic sql, you also have to add explicit table permissions.So, do you really need to do this? Is this just for presentation purposes in Query Analyzer? If not, then can't you handle this in your application?Tara |
|
|
|
tool
Starting Member
26 Posts |
Posted - 2003-10-14 : 16:17:27
|
| Actually I wanted to do it because I am working on an application that requires some excel exports. Excel can convert an html table, so my goal was to create a generic export page that just contains a datagrid that can be bound to any sproc result. Then in the db I would have a table containing the export names and corresponding sproc names. This way when users request an additional export it's just a matter of creating the sproc and adding a record to the table. Some of the exports are things like "detail for the last 12 months" so for this to work I need to have the column names be dynamic based on what the current month is.The only other option I can think of besides dynamic sql (which I'm not to keen on) is using a case statement with the query repeated 12 times (once for each possible current month). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-14 : 16:21:09
|
quote:
Originally posted by tool
Some of the exports are things like "detail for the last 12 months" so for this to work I need to have the column names be dynamic based on what the current month is.The only other option I can think of besides dynamic sql (which I'm not to keen on) is using a case statement with the query repeated 12 times (once for each possible current month).
A CASE statement would be recommended. Dynamic sql would make your code shorter, but that doesn't mean that it should be done.Tara |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-10-14 : 23:13:03
|
| If you are calling the query from an app that you are writing I would use a standard, non-dynamic query in your SQL and let your application handle renaming the columns at export time.That was you get the flexability of dynamic sql without the kludge of repeating your query 12 times.Damian |
|
|
|
|
|
|
|
|
Variable column alias