variable in column name - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

variable in column name

Author

Topic

oceanboy
Starting Member

44 Posts

Posted - 2007-04-11 : 06:31:40

Hello!

I am wondering if it is possible to write a user-defined function where it can do the following:


@position = 1 (or any input value)
@value = select column + @position from table
..#12290;
...
return ...
...

to retrieve information from the column requested

thanks!~
OB

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-11 : 06:34:41

No..it's not possible because it will require Dynamic sql which is not allowed inside UDF.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 06:53:57

You just want to add the input value (parameter) to a column in a table and return the sum?

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-11 : 07:06:07

Ah! That sounds reasonable...but why would anybody want to create function to do that...he can do that without function as well...but then of course, we don't know about his business rules !

It's time to change my spects, I believe

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 07:18:40

Well, I don't know what all the ... are for either.
Maybe OceanBoy will come back to explain, maybe not.

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-11 : 08:08:24

Looks like he has an unnormalized schema ... I think he has a table like this:

ID, Val1, Val2, Val3, Val4 ....

and he wants to pass in a value of "n" and return column "Valn".

The answer: NORMALIZE your database! this is a really, really bad design.

More here: http://www.datamodel.org/NormalizationRules.html

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 08:20:55

Until he finds the time to normalize the database, he could use

SELECT @Value = CASE
WHEN @Column = 1 THEN Val1
WHEN @Column = 2 THEN Val2
WHEN @Column = 3 THEN Val3
WHEN @Column = 4 THEN Val4
END
FROM Table
...
WHERE ...

Peter Larsson
Helsingborg, Sweden

oceanboy
Starting Member

44 Posts

Posted - 2007-04-11 : 22:24:59

hello everyone!

jsmith8858 got me right! That is exactly what I wanted to do. Basically, ID-Val1-Val2-Val3 is supposed to be unique. The function I want to write is for another table, where the table design is the same but it contains all the possible combination from the original table. That means the original table has a relation to itself. (Or if any of you have a better way to do this?)

I was thinking about the solution and it turned out to be similar to Peso's solution!

Will try it out.

OB

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-12 : 09:01:15

quote:
Originally posted by oceanboy

hello everyone!

jsmith8858 got me right! That is exactly what I wanted to do. Basically, ID-Val1-Val2-Val3 is supposed to be unique. The function I want to write is for another table, where the table design is the same but it contains all the possible combination from the original table. That means the original table has a relation to itself. (Or if any of you have a better way to do this?)

I was thinking about the solution and it turned out to be similar to Peso's solution! Will try it out.

OB

PLEASE make sure that you read the link I provided and read up on "database normalization". You have a poor database design which is why writing simple SQL statements is more complicated and less efficient than it needs to be. I am not sure if you can change your design, but it is still something to be aware of if you are working in the database field. Data integrity and other issues arise -- in addition to overcomplicated and inefficient sql statements -- when you have a poor database design.

- Jeff
http://weblogs.sqlteam.com/JeffS

oceanboy
Starting Member

44 Posts

Posted - 2007-04-13 : 00:51:38

jsmith8858, i won't disagree with you regarding the design but it is not in my power to redesign it, it will affect much more work than what i wanted to do. so, i have to find away to get around what i wanted to do. But thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources