January 20, 2007 at 9:53 am
I am very new to sql server and am looking for some help.
I am trying to use a variable as a column name in a select. Is this possible?
Example:
declare @objname varchar(50)
set @objname = 'Y00P01' <----- Y00P01 is the column name in sales table
select @objname
from sales
Thanks.
January 20, 2007 at 4:35 pm
Allen - when you want to do something like this you enter into the realms of "dynamic SQL"...to read up on this and why you should try and avoid using it, pl. go to this link - the curse and blessings of dynamic sql
you would essentially have to exec your select like this when you want to use a column as a variable name...
exec('select ' + @objname + ' from myTable')
**ASCII stupid question, get a stupid ANSI !!!**
January 21, 2007 at 8:02 am
Thank you. I will read that article. As I use it I already see why it is not much fun. Now I am trying to take that select and use it as an update.
update table set field1 = exec('select ' + @objname + ' from myTable')
Back to the books!
January 22, 2007 at 12:40 pm
I don't mean to pick at this on you, but it sounds like you are a bit new at this and dynamic SQL is usually not a good place to start. In fact, some would say it is a last resort.
Now I don't want to turn this thread into an argument about when to use dynamic SQL, but if you post a bit more about what you are really trying to do you may get some ideas of a better way of doing it.
Depending on the version of SQL server you are running and the application environment, you may be hurting performance or security without cause.
Lot's of people here are willing to offer solutions.
January 22, 2007 at 2:58 pm
AJ,
If you want some experience in using Dynamic SQL, take a look at some code that was posted here a while back by aclaver1 on 12-FEB-2004. I've adapted a version of this for my own use, but basically it creates an Excel or HTML file thru a SQL proc that loops through Information_Schema.Columns to find out the names of the columns and their datatypes for a particular table - passed in as a variable.
In my case, the column names in this particular table would change every week - making standard processing via a DTS pkg difficult, if not impossible. Take a look at the code listed on the link. One word of warning, although the code is all SQL, the comments are in Spanish. Fortunately, I work with several at the office who are fluent in spanish, but they are not programmers.. so many of the "code-speak" we use did not make sense to my translators.
Here's the link, perhaps you can borrow some snippets to do what you want:
http://qa.sqlservercentral.com/scripts/listscriptsbyauthor.asp?author=116457
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply