Column Name as a Parameter Value

Question

Column Name as a Parameter Value

cdun2

SSCertifiable

Points: 7893
More actions
September 14, 2006 at 12:52 pm

#114793

Hello,
I'm trying to set up a proc in which the parameter value would actually be the name of a table column. The parameter value would be used as the column name in a SELECT statement.
In this case, there are twelve possible column names from a single table that could populate the parameter, and the datatype for all columns is INT.
How might I go about doing this? Do you have enough information about what I am trying to do?
Thank you for your help!
CSDunn

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

If its a straight select statement and that fact that you just have 12 columns I would use a case statement. The reason being sp is complied.

If the sp has more than select statement I would use dynamic SQL.

Hope this helps

Thanks

Sreejith

cdun2 SSCertifiable Points: 7893 More actions · Answer 2

Thank you for your help. I had thought about this approach, and will look into it further.

CSDunn

Vladan SSC-Insane Points: 22072 More actions · Answer 3

I suggest to read some articles on http://www.sommarskog.se, dealing with Dynamic SQL and usage of parameters in stored procedures, before you decide which way to go.

cdun2 SSCertifiable Points: 7893 More actions · Answer 4

Thanks, I'll do that. What if I wanted to set up a UDF that would take a table field name as its parameter, and return the table field needed in the SELECT statement? What would the return type be?

Thanks again!

CSDunn

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 5

Return type should be sysname, It is the nvarchar(128) datatype used to store the object names in SQL Server