dowt in variable(stored procedure)

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)

dowt in variable(stored procedure)

Author

Topic

iswan
Starting Member

28 Posts

Posted - 2007-07-24 : 03:48:12

In procedure field name I get as argument. I want to select that field without using exec()

Ex:
Table : Student
SName
Isw
John

Query: select SName from Student. I am getting the SName correctly

Problem:

set @Str='SName'
select @Str from Student

Output:

(No Column Name)
SName

But Expected OutPut:

SName
Isw
John

Regards
Iswan

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-07-24 : 04:13:02

You have to use dynamic sql...

Exec('Select ' + @str +' From student')

--------------------------------------------------
S.Ahamed

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-24 : 04:52:26

quote:
In procedure field name I get as argument. I want to select that field without using exec()

OK. In that case don't use exec() use sp_executesql instead.

KH
[spoiler]Time is always against us[/spoiler]

iswan
Starting Member

28 Posts

Posted - 2007-07-24 : 04:54:48

With out using Exec()
any other way is available?

Iswan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-24 : 05:00:20

yes. use sp_executesql. What you want involved dynamic SQL. Unless you can do form the query in your front end application (not really advisable), you can only resort to using exec() or sp_executesql. Of these 2 option, sp_executesql is preferred.

The main question, why do you need to do this ?

Before going to the Dynamic SQL path, better read this http://www.sommarskog.se/dynamic_sql.html

KH
[spoiler]Time is always against us[/spoiler]

iswan
Starting Member

28 Posts

Posted - 2007-07-24 : 05:13:13

I need all the selected rows. using that I will process. But If I use exec() or sp_executesql(). How can I do?

Ex:
select @TempStr=Coalesce(@TempStr+ '~','')+SName from Student
PRINT @TempStr

But the field name is variable How can I get this?

set @Str='SName'
select @TempStr=Coalesce(@TempStr+ '~','')+@Str from Student
PRINT @TempStr

@Str - is field name

Iswan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-24 : 05:18:31

quote:
I need all the selected rows.

You mean columns ?

KH
[spoiler]Time is always against us[/spoiler]

iswan
Starting Member

28 Posts

Posted - 2007-07-24 : 05:27:27

Yes, It is column

Iswan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-24 : 05:33:42

You can explain again, what is the purpose of all these ? What are you trying to achieve ?

KH
[spoiler]Time is always against us[/spoiler]

iswan
Starting Member

28 Posts

Posted - 2007-07-24 : 05:42:53

My Table like: It contains number of column
EName Eno .....
Iswan 001
Raj 002
John 003

From the Front End, They give the column name

Ex: scspNew('EName','Eno',..) They will call procedure

I find that column and combine the data with '~'

Ex: Need 'Iswan~001'. then call another procedure with this arg

scspDataInsert('Iswan~001')
scspDataInsert('Raj~002')

So I need the argument like 'Iswan~001' (Output), Input is fieldname

Iswan

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-07-25 : 01:52:43

Oh. My. God.
Combining this with your other question makes me want to suggest you look carefully at your architecture & design.
Then scrap it and do it properly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources