Dynamically selecting column
-
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
-
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
-
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
-
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] -
I knew you had Erland's link (and I didn't) so I didn't mention it.
/Kenneth
-
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] -
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] -
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 -
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
-
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
-
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply