Dynamically selecting column

Question

Post reply

Dynamically selecting column

dcross

SSC Enthusiast

Points: 133
More actions
April 15, 2004 at 5:55 pm

#108127
I have a sproc that has a parameter passed to it with the name of one of the columns I need to select. I was hoping for something like the following (This code obviously does not work, it's just for illustration purposes):
```
CREATE PROCEDURE GetRows
```
```
@Column
```
```
AS
```
```
SELECT ID, @Column FROM Table1
```
```
GO
```
Any help anyone can provide is greatly appreciated.
Thank you,
DC Ross

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

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

ekalos SSC Journeyman Points: 75 More actions · Answer 1

Try this guy out....not pretty, but might work for you.

CREATE PROCEDURE GetRows

@Column varchar(25)

AS

DECLARE @sql Varchar(50)

SET @sql = 'SELECT ' + @Column + ' FROM Table1'

EXEC (@Sql)

GO

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

Just be aware that using dynamic SQL will not reuse cached queryplans, the user needs permissions on the basetable itslef, and as written opens you up for injection attacks.

Dynamic SQL should be avoided unless absolutely necessary, and you have a full understanding of what you're getting into by using it.

=;o)

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

To add to Kenneth (and maybe posted by me for the 1000th time) this one is VERY interesting

http://www.sommarskog.se/dynamic_sql.html

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

I knew you had Erland's link (and I didn't) so I didn't mention it.

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

You're soo kind, Kenneth

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Btw, why don't you have it. Aren't you both Swedish and MVPs?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 7

Well, It is a very valid point that it is better to use dynamic SQL to bare minimum or if possible not to use at all but like in inevitable conditions what would be the best approch.Well you will definitely ask me to quote an instance where it may be inevitable, but in practical scenerio's there would be many conditions where it may have to be decided dynamically, Would you feel using Multiple case statements inside a Query is better or a dynamic parameter to decide the column that needs to be fetched is better.

Thanks

Prasad Bhogadi
www.inforaise.com

dcross SSC Enthusiast Points: 133 More actions · Answer 8

Thanks very much for the link, it was very informative (if not a bit above my head ).

I restructured my query based on some info from that article:

CREATE PROCEDURE GetRows 
 @Column varchar(20)
AS

SELECT ID,

 CASE @Column

  WHEN 'Col1' THEN
   Col1

  WHEN 'Col2' THEN
   Col2

  WHEN 'Col3' Then
   Col3
 END

FROM Table

GO

Do you think this would do the trick?

Thanks again for your help!

-DC Ross

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 9

True on both counts

I guess it's like post-it's - they tend to get lost in the overall-mess that's my desk. (the links that is)

/Kenneth

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 10

This will depend on the columns datatypes within the case.

Be aware that a CASE can only return one datatype - which one (if no casting is done) is decided upon datatype precedence among the involved columns. If there should be any that cannot be implicitly converted, then you get an error.

Ways around this is to make sure that all columns will resolve to the same datatype - ie pust some cast's or converts in there.

/Kenneth