Passing the field name as a paramter to the select SQL? - MittyKo

Hi All

I would like to pass the field name as a paramter to the below SQL to get
all the values for that filed:

--Table  #Tb1
Create table #Tb1 ( A int IDENTITY(1,1) ,B varchar(10), C varchar(10), D
varchar(10))
insert into #Tb1 (B,C) values ('a1','')
insert into #Tb1 (B,D) values ('b2','')
insert into #Tb1 (C,D) values ('','d3')

--SQL
declare  @Column char(5)
set @Column = 'D'
select @Column   from #Tb1
--Values
D
D
D

--What i am trying to achieve by passing the fieldname as a parameter.
--select D from #Tb1
--Right values
D
Null

d3

I get Ds instead of the real values, but when i run 'select D from #Tb1' i
get the real values. What am i doing wrong or is there a way to get the real
values for column D by passing the field name as a parameter to the SQL?

Thank you in advance

Passing the field name as a paramter to the select SQL? - Mike C#

You need to use dynamic SQL to get what you want out of this.  Check out the
EXEC statement and the sp_executesql procedure in BOL.

Passing the field name as a paramter to the select SQL? - Steve Dassin

You're thinking right, the problem is sql isn't :-)
To pass an argument you have to declare a type for it, computer science 101.
A column 'type' doesn't really make sense. But a table 'type' does.

http://beyondsql.blogspot.com/2007/06/dataphor-13-passing-table-as-parameter.html
http://beyondsql.blogspot.com/2007/08/dataphor-creating-super-function.html

Because sql only recognizes (loosely) numbers and strings as types your
SOL and forced to make up for this huge hole with dynamic sql :( :)

www.beyondsql.blogspot.com

Passing the field name as a paramter to the select SQL? - M

As Mike C# mentioned in his post one option is dynamic SQL.  I strongly
suggest reading this fine article by Erland Sommarskog on the subject:
http://www.sommarskog.se/dynamic_sql.html

On the other hand, if you explain your actual goal (in business terms) in
more detail perhaps we can help you find an even more favorable solution.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

Passing the field name as a paramter to the select SQL? - Madhivanan

On Nov 5, 10:59 pm, MittyKom <Mitty...@>

You should avoid passing object names as parameter
Make sure you read sommarskog's article fully

Passing the field name as a paramter to the select SQL? - Madhivanan

Steve

All of your links are redirected to the same page

Passing the field name as a paramter to the select SQL? - Steve Dassin

Hi,

Yep, just scroll down to the article. The link will be the article after all
the titles/labels :)

Passing the field name as a paramter to the select SQL? - Mikhail Berlyant

At least for fun try this:

CREATE TABLE #Tb1 ( A int IDENTITY(1,1) ,B varchar(10), C varchar(10), D
varchar(10))
INSERT INTO #Tb1 (B,C) VALUES ('a1','')
INSERT INTO #Tb1 (B,D) VALUES ('b2','')
INSERT INTO #Tb1 (C,D) VALUES ('','d3')

DECLARE @Column char(5)
SET @Column = 'D'

DECLARE @x xml
SET @x = (SELECT * FROM #Tb1 FOR XML PATH, TYPE)
SELECT
t.c.value('(./../A/text())[1]', 'int') as a ,
t.c.value('(./text())[1]', 'varchar(10)') as r
FROM
@x.nodes('/row/*') as t(c)
WHERE
t.c.value('local-name(.)', 'varchar(5)') = @Column

DROP TABLE #Tb1

--
Mikhail Berlyant
Senior Data Architect
MySpace.com