Dynamic select query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic select query

Author

Topic

prichintha
Starting Member

2 Posts

Posted - 2002-12-09 : 16:42:31

Hi,

I need to pass the column name to a SELECT dynamically and assign the value of that column to a variable in a stored procedure. This is what I hav done

SELECT @columnValue = @columnName from station_tbl
where station_no = 1783

@columnName is passed in dynamically,

Eg:
@columnName = station_no

SELECT @columnValue = @columnName from station_tbl
where station_no = 1783
print 'columnValue is =' + @columnValue

which returns

columnValue is = station_no

instead of

columnValue is = 1783

Could anybody tell me what is wrong and what I need to do to get value of the column instead of column name

Any effort to help me out is greatly appreciated.
Thanks,

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-09 : 17:55:03

You need to use dynamic sql

Take a look
http://www.sqlteam.com/item.asp?ItemID=4619

DECLARE @SQL NVARCHAR(1000)

SET @SQL = N'SELECT ' + @columnvalue + N' = ' + @columnName +
N'FROM station_tbl ' +
N'WHERE station_no = 1783'

Exec sp_executesql @SQL

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-12-09 : 17:55:05

http://www.sqlteam.com/FAQ.asp

Should help.....

Damian

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-09 : 17:55:34

2 seconds. Wow.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-12-09 : 17:55:48

Wow, you sniped me by 2 seconds

Damian

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-12-09 : 17:56:10

Again!

Damian

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-09 : 17:59:24

quote:

I need to pass the column name to a SELECT dynamically and assign the value of that column to a variable in a stored procedure.

Am I missing something here? Shouldn't you be using an UPDATE rather than a SELECT? I also think you have your assignment the wrong way round? Shouldn't it be @columnName = @columnValue?

update station_tbl
set ' + @columnName + ' = ' + @columnValue + '
where station_no = 1783'

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-09 : 18:00:45

quote:

Again!

Damian

Must be the delay from downunder....damn

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

prichintha
Starting Member

2 Posts

Posted - 2002-12-10 : 18:32:45

Hi,

I guess I wasnt very clear with my question. Here is exactly what I do.

SELECT @columnValue = @columnName from station_tbl
where station_no = @orgStation_no

I pass in the column name to be retrieved along with the station_no for which I need the information. I need to store this in @columnvalue.

I tried what Valter had suggested but that didnt work for me. This is what I did.

Set @columnName = COL_NAME(OBJECT_ID('station_tbl'),@columnCount)
declare @sql NVARCHAR(1000)
set @sql = N'select' + @columnValue+ N' =' + @columnName
+N'from station_tbl where station_no ='+ @orgStation_no
Exec sp_executesql @SQL
print @columnValue

@columnValue remains NULL. Also could anybody tell me why we append N before the single quote.

Thanks

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-10 : 18:48:28

N indicates that the data is unicode.

quote:

I tried what Valter had suggested but that didnt work for me. This is what I did.

I don't see a big difference.

Mine
DECLARE @SQL NVARCHAR(1000)

SET @SQL = N'SELECT ' + @columnvalue + N' = ' + @columnName +
N'FROM station_tbl ' +
N'WHERE station_no = 1783'

Exec sp_executesql @SQL

Yours

'Is this the big difference
Set @columnName = COL_NAME(OBJECT_ID('station_tbl'),@columnCount)

declare @sql NVARCHAR(1000)
set @sql = N'select' + @columnValue+ N' =' + @columnName
+N'from station_tbl where station_no ='+ @orgStation_no <--- Difference

Exec sp_executesql @SQL

If you want better help you have to provide a good explanation of what you're trying to do and some sample data and expected results.

Edited by - ValterBorges on 12/10/2002 18:56:25

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-10 : 19:08:13

Assuming that the # of columns and their names in your table remain constant (hopefully!), you might get better results with:

SELECT @columnValue = CASE @ColumnName
WHEN 'Field1' THEN Field1
WHEN 'FIeld2' THEN Field2
....
etc
ELSE Null
END
from station_tbl
where station_no = @orgStation_no

Easier to understand and debug. Plus you won't get errors if the column passed doesn't exist.

Also: Why does this stored proc exist? why not just SELECT what you need from the table or view?

(just my $.02)

Good luck!

- Jeff

Edited by - jsmith8858 on 12/10/2002 19:21:37

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources