March 26, 2003 at 3:21 pm
Is there anyway to code a column name as a variable in a select statement? For example:
Create Proc getTitles
@colid varchar(10)
As
Select title,@colid from titles
where title_id Like 'P%'
order by @colid
When I Exec getTitles Price, I get all the titles and the text "Price" next to each title. I have so many queries that are identical except for the column name I want to select. Any ideas?
March 26, 2003 at 9:27 pm
I think, in genral, you can't use a variable anywhere where it would effect the query plan, so col names and tbale names are out.
but if your not too concerned about optimizing performance for this query then you can just use:
--declare @colid varchar(128)
declare @sql varchar(8000)
SET @sql =
'Select title,' + @colid + ' from titles
where title_id Like ''P%''
order by ' + @colid
exec (@sql)
March 27, 2003 at 7:54 am
Thanks, I appreciate it. I am a converted Oracle DBA. This is a snap in Oracle.
March 27, 2003 at 8:25 am
I gave this URL earlier today in another thread, it is probably the best article on dynamic sql in SQL Server.
http://www.algonet.se/~sommar/dynamic_sql.html
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply