retrieve Column Names from Table and Used in QUERY

Question

Post reply

retrieve Column Names from Table and Used in QUERY

parth83.rawal

SSCommitted

Points: 1858
More actions
November 24, 2010 at 11:06 am

#229560

Hi,
I have Column Names in a SQL Table.
Now I want to use those Column Names in my Query but the issue which i am facing right now is
while executing the Query in Function some how i am getting it as string (as my return type of the function is char 🙂
Now What i want is to retrieve the Column Name and Use them in my query
1. SET @ColName=" Function which will return the Column name"
2. Select @Result=Select @Colname from <my Table>
Thanks in advance
Parth Rawal

Viewing 11 posts - 1 through 10 (of 10 total)

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

Cory E. SSCertifiable Points: 7076 More actions · Answer 1

1. SET @ColName=" Function which will return the Column name"

2. Select @Result='Select ' + @Colname + ' from <my Table>'

-- Cory

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

You will have to use Dynamic SQL for the same

EXECUTE sp_executeSQL @result

Check for sp_executeSQL in Books Online or Google.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

parth83.rawal SSCommitted Points: 1858 More actions · Answer 3

Thanks a ton,

actually i have used both the answers in my stored procedure, one que though when i tried using the execute statement in my function it gave me an error as i can execute anything in function 🙁

Now I had tried using the "OPenQuery" but that also didint work properly, can anyone tell me how can i run the same in my function.

Thanks

Parth

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

You can't use Dynamic queries and temporary tables in a function. So, if you have to implement the Dynamic SQL you will have to use it in a stored procedure.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

parth83.rawal SSCommitted Points: 1858 More actions · Answer 5

That is what i am doing...:)

Anyways really thanks for the reply. This makes me confident about my approach 🙂

Thanks

Parth

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 6

parth83.rawal (11/25/2010)
That is what i am doing...:)
Anyways really thanks for the reply. This makes me confident about my approach 🙂
Thanks
Parth

You'll have to understand that we didn't think so because of what you wrote...

parth83.rawal (11/25/2010)
...when i tried using the execute statement in my function it gave me an error as i can execute anything in function..

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

parth83.rawal SSCommitted Points: 1858 More actions · Answer 7

My Mistake and typo, as I meant to say CANT "Execute" anything in Function 🙂

sivaj2k Ten Centuries Points: 1188 More actions · Answer 8

Hi

1. SET @ColName= select function_name (@parameter)

2. Select @Result=' Select '+ @Colname +' from <my Table> '

3. execute (@Result)

Thanks

Siva Kumar

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 9

I would really like to hear you're logic behind this, it sounds like bad design to me.

Clear Sky SQL
My Blog[/url]

sivaj2k Ten Centuries Points: 1188 More actions · Answer 10

Hi Dave

I think the query is answered!!!, would you need any more explanation about those 3 lines of code.

Regards

Siva Kumar J