using variable for field name in select statement

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

SQL Server Development (2000)

using variable for field name in select statement

Author

Topic

kate_cha
Starting Member

7 Posts

Posted - 2005-09-05 : 06:41:46

I want to write a stored procedure that will allow me to pass in the field name and select results depending on that field name.

ie
I want to get the values of uid,firstname, secondname from the customers table where custId= a specific number.

I could do it
----------------------------------------------
** select uid from customers where custID=1231
** select firstname from customers where custID=1231
** select secondname from customers where custID=1231
-----------------------------------------------------

for a variety of reasons the select statement is more complicated tahn this, and I'd prefer not to have to replicate the code several times, so I've been trying to do it like this:

----------------
pass @field,@custID into stored procedure
** select @field from customers where uid=@custID
-----------------

the idea being that I can call it several times just writing

-----------------------
** exec mysp 'uid','1231'
------------------------

but when I write the select statement like this, instead of returning the uid (eg 1) as I would have expected it's returning the name of the field, (ie uid).

any ideas how I can use a variable in the select statement to return the correct value?

Thanks!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-05 : 06:51:20

Try this

Exec('select '+@field+' from customers where uid='+@custID)

Madhivanan

Failing to plan is Planning to fail

kate_cha
Starting Member

7 Posts

Posted - 2005-09-05 : 10:49:06

how would I store the value it returned in another variable though?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-09-05 : 10:53:41

Save it in a table which is accessible outside the scope of the EXEC statement.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-05 : 10:54:10

[code]
use northwind
declare @value int
declare @sql nvarchar(50)
set @sql = 'orderid'
set @sql = 'select top 1 @value = ' + @sql + ' from orders'

select @sql
exec sp_executesql @sql, N'@value int out', @value out
select @value
[/code]

Go with the flow & have fun! Else fight the flow

kate_cha
Starting Member

7 Posts

Posted - 2005-09-05 : 11:23:13

thanks!! that works great!!

Kate

kate_cha
Starting Member

7 Posts

Posted - 2005-09-06 : 06:04:16

I've come across another issue to tax your brains with....

using the sp_executeSQL function as you suggested, I've written

'****************************************
declare @fieldvalue nvarchar(1000),
@sql nvarchar(255)

set @sql= 'select @fieldvalue=price from products where plu =''' + '9780711223905' + ''''
exec sp_executesql @sql,N'@fieldvalue nvarchar out',@fieldvalue out

'***************************************

the value it returns is 9 - it should however be 9.99......
The value is an nvarchar in the db.

if I try:

'*******************************************
declare @fieldvalue nvarchar(255)
select @fieldvalue=price from products where plu ='9780711223905'
select @fieldvalue
'******************************************

it returns the correct value....

any reason why it's truncating the value using sp_executeSQL?

thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-06 : 06:11:38

your missing nvarchar length
exec sp_executesql @sql,N'@fieldvalue nvarchar(255) out',@fieldvalue out

Go with the flow & have fun! Else fight the flow

kate_cha
Starting Member

7 Posts

Posted - 2005-09-06 : 06:13:36

my saviour!! you just saved my head from exploding.

thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources