passing a field name into a parameter

say I have a table with field1,field2,field3,...

and have a SP with a parameter @.myfield

is there a way I can do

Select @.myfield from Table1

thanks for your helpcreate procedure sp_fld (@.inFld varchar(50))
as
begin
declare @.sql varchar(200)
declare @.nsql nvarchar(200)

set @.sql = 'SELECT ' + @.inFld + ' FROM table'
set @.nsql = cast(@.sql as nvarchar(200))

exec sp_executesql @.nsql
end
--
David Rowland
http://dbmonitor.tripod.com|||Don (ir_don@.yahoo.com) writes:
> say I have a table with field1,field2,field3,...
> and have a SP with a parameter @.myfield
> is there a way I can do
> Select @.myfield from Table1

SELECT CASE @.myfield
WHEN 'field1' THEN field1
WHEN 'field2' THEN field2
...
END
FROM tbl

Another post suggested using dynamic SQL, but that's a poor solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Use CASE expressions. But if you have the correct design you probably
won't want to do that very often. It isn't a good idea to use column
names to encode data.

--
David Portas
SQL Server MVP
--|||Yes, there is. But before you do this, please get any book on **basic
software engineering** and look up the concept of cohesion in a code
module.|||Erland Sommarskog wrote:
> Don (ir_don@.yahoo.com) writes:
> > say I have a table with field1,field2,field3,...
> > and have a SP with a parameter @.myfield
> > is there a way I can do
> > Select @.myfield from Table1
> SELECT CASE @.myfield
> WHEN 'field1' THEN field1
> WHEN 'field2' THEN field2
> ...
> END
> FROM tbl
> Another post suggested using dynamic SQL, but that's a poor solution.

Except for the fact that the dynamic SQL still works if your columns
are different datatimes.

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||dbmonitor (dbmonitor_support@.hotmail.com) writes:
> Erland Sommarskog wrote:
>> Don (ir_don@.yahoo.com) writes:
>> > say I have a table with field1,field2,field3,...
>>> > and have a SP with a parameter @.myfield
>>> > is there a way I can do
>>> > Select @.myfield from Table1
>>
>> SELECT CASE @.myfield
>> WHEN 'field1' THEN field1
>> WHEN 'field2' THEN field2
>> ...
>> END
>> FROM tbl
>>
>> Another post suggested using dynamic SQL, but that's a poor solution.
> Except for the fact that the dynamic SQL still works if your columns
> are different datatimes.

In such case I would question the sanity of the procedure at all. It
would be easier to just sent the SQL statement from the client.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> dbmonitor (dbmonitor_support@.hotmail.com) writes:
> > Erland Sommarskog wrote:
> >> Don (ir_don@.yahoo.com) writes:
> >> > say I have a table with field1,field2,field3,...
> >> >> > and have a SP with a parameter @.myfield
> >> >> > is there a way I can do
> >> >> > Select @.myfield from Table1
> >>
> >> SELECT CASE @.myfield
> >> WHEN 'field1' THEN field1
> >> WHEN 'field2' THEN field2
> >> ...
> >> END
> >> FROM tbl
> >>
> >> Another post suggested using dynamic SQL, but that's a poor
solution.
> > Except for the fact that the dynamic SQL still works if your
columns
> > are different datatimes.
> In such case I would question the sanity of the procedure at all. It
> would be easier to just sent the SQL statement from the client.
You have no arguement from me over that statement.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp