how to use variable in 'select' command

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)

how to use variable in 'select' command

Author

Topic

myheart46
Starting Member

13 Posts

Posted - 2003-12-03 : 05:47:44

my function is
declare
xx varchar2(100);
temp varchar2(100);
begin
xx:='name';
select xx into temp
from member;
return(temp);
end;

this function not select field 'name' but it select text name
but in sqp plus i can do it becoz of '&' command
for examples
select &xx from member;
and then i give xx = name . It okly
plzz help me thxu
ps.i want to use it in report bulider

SqlStar
Posting Yak Master

121 Posts

Posted - 2003-12-03 : 08:38:15

Hi,

U have to use "Dynamic SQL".
In SQL Server,

begin
declare @xx varchar(100), @temp varchar(100)
SET @xx = 'name'
SET @xx = 'Select ' + @xx + ' Into temp From member'
exec(@xx)
Select * from temp
end

NOTE : U have to drop that "temp" table externally like "drop table temp", otherwise it will give an error when execute next time.

:) While we stop to think, we often miss our opportunity :)

myheart46
Starting Member

13 Posts

Posted - 2003-12-04 : 00:04:50

declare
temp varchar2(100);
temp2 varchar2(100);
x varchar2(100);
begin
temp :='so_nbr';
temp2 :='select'+ temp +'into x
from so_mstr';
exec(temp2);
message(temp2);
end;

but it has error at 'exec' it say function not be declare
help me plzz

mohit_sharan
Starting Member

22 Posts

Posted - 2003-12-04 : 00:18:44

Hay,

Why dont u take the help of dynamic sql to built the querey and then execute the query with the help of execute immediate in SQL Plus. I think it will solve u r problem.

-Mohit.

myheart46
Starting Member

13 Posts

Posted - 2003-12-04 : 00:48:32

how i use sql that u call 'dynamic sql' in report builder ??
some reason that i should use this code becoz
of my field intable has some condition
for example...
field name comment_01,comment_02.... comment_15
and i want to use loop for select this field
-->comment_||:varlable
my english is not good sorry about that

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources