Passing A Field Name as a Parameter (Dynamic SQL?)

I'm writing a Stored-Procedure and Id like to pass a field name as a
parameter. It seems simple enough but I can't seem to find the
solution.
Here is a shortened example of the procedure
Select Profiles.Name, Profiles.State, Profiles.City, @.ProfileColumn,
Profiles.County FROM Profiles WHERE State = 'CT'
The @.ProfileColumn is simple a field name in the DB table. The actual
select statement is 5 times as large and consists of many InnerJoins
but I shortened it here for display reasons.
Is there a simply way to do this?You will have to do this with dynamic SQL. It is generally considered a bad
idea to do this in a stored procedure, but it certainly may be fine in
yours.
I am a bit concerned as to how many columns, and if you are spending time in
the joins going to get data that might not be used (like are all of the
columns in the same table or spread around?)
If they are all in one table, and depending on the number of rows, then you
might consider using a temp table to hold the results of the non-dynamic
parts, and then join in the dynamic parts.
If they are spread around, then dynamic SQL might be the best way to go,
since you could possibly eliminate some of the joins if columns aren't
needed.
Just thoughts...
<funphxnaz@.aol.com> wrote in message
news:1137462325.499202.325310@.g44g2000cwa.googlegroups.com...
> I'm writing a Stored-Procedure and Id like to pass a field name as a
> parameter. It seems simple enough but I can't seem to find the
> solution.
> Here is a shortened example of the procedure
>
> Select Profiles.Name, Profiles.State, Profiles.City, @.ProfileColumn,
> Profiles.County FROM Profiles WHERE State = 'CT'
> The @.ProfileColumn is simple a field name in the DB table. The actual
> select statement is 5 times as large and consists of many InnerJoins
> but I shortened it here for display reasons.
> Is there a simply way to do this?
>|||<<Build a VIEW with all the columns you want to see, then filter out
what
you do not need in the front end, where display is supposed to be done.
I actually started this way but the VIEW would have thousands of rows.
<<<< I am a bit concerned as to how many columns, and if you are
spending time in
the joins going to get data that might not be used (like are all of the
columns in the same table or spread around?)>>>>
The columns are spread around many tables. A total of 30 columns + 2 I
am trying to set dynamically.|||Found this:
http://www.sommarskog.se/dyn-search.html
It was helpful.|||(funphxnaz@.aol.com) writes:
> I'm writing a Stored-Procedure and Id like to pass a field name as a
> parameter. It seems simple enough but I can't seem to find the
> solution.
> Here is a shortened example of the procedure
>
> Select Profiles.Name, Profiles.State, Profiles.City, @.ProfileColumn,
> Profiles.County FROM Profiles WHERE State = 'CT'
> The @.ProfileColumn is simple a field name in the DB table. The actual
> select statement is 5 times as large and consists of many InnerJoins
> but I shortened it here for display reasons.
> Is there a simply way to do this?
ProfileColumn = CASE @.ProfileColumn
WHEN 'thiscolumn' THEN 'thiscolumn'
WHEN 'thatcolumn' THEN 'thatcolumn'
..
END
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx