Pls help...How to pass a executed value to a varia

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)

Pls help...How to pass a executed value to a varia

Author

Topic

nashlovebrown
Starting Member

1 Post

Posted - 2006-09-17 : 22:23:03

Pls help...How to pass a executed value to a variable?

hi everyone im new here..
ok im having prob in passing the executed value of a string into a variable for me to manupulate the value. sample codes as below :

declare @Str varchar(500)
declare @Str1 varchar(50)
declare @Int varchar(50)
declare @Str2 varchar(50)

set @Str1 = 'where ET.isVerified = 1'
set @Str = 'select sum(ET.Sundries+ET.Entertainment+ET.BalReimbursement+ET.Fee+ET.TrainerCharges+ET.SeminarPack+ET.Food+ET.Accomodation+ET.Flight) from ExpensesTransactions ET ' + @Str1

EXEC(@Str)

Answer : 16935.00

so now i want to pass the output(Answer) into a variable so that i can insert the value into a table. anyone can help me in this thing . thank you so much..

regards
-nash-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-17 : 22:26:50

You are you using Dynamic SQL for the query ?

It will be much easier to do it without Dynamic SQL


select @answer = sum(ET.Sundries + ET.Entertainment + ET.BalReimbursement + ET.Fee+ET.TrainerCharges + 
                     ET.SeminarPack+ET.Food + ET.Accomodation + ET.Flight) 
from   ExpensesTransactions ET
where  ET.isVerified = 1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-17 : 22:29:13

To do it the dynamic SQL way, you have to use sp_executesql instead of exec()


set @Str = 'select @anwser = sum(ET.Sundries+ET.Entertainment + ET.BalReimbursement + ET.Fee + 
ET.TrainerCharges+ET.SeminarPack+ET.Food+ET.Accomodation+ET.Flight) 
from ExpensesTransactions ET ' + @Str1
declare @answer int

exec sp_executesql @Str, '@answer int OUTPUT', @answer OUTPUT

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-18 : 02:00:21

quote:

Originally posted by khtan

To do it the dynamic SQL way, you have to use sp_executesql instead of exec()


set @Str = 'select @anwser = sum(ET.Sundries+ET.Entertainment + ET.BalReimbursement + ET.Fee + 
ET.TrainerCharges+ET.SeminarPack+ET.Food+ET.Accomodation+ET.Flight) 
from ExpensesTransactions ET ' + @Str1
declare @answer int

exec sp_executesql @Str, '@answer int OUTPUT', @answer OUTPUT

Just add N before sql string to make it nvarchar:


set @Str = N'select @anwser = sum(ET.Sundries+ET.Entertainment + ET.BalReimbursement + ET.Fee + 
ET.TrainerCharges+ET.SeminarPack+ET.Food+ET.Accomodation+ET.Flight) 
from ExpensesTransactions ET ' + @Str1
declare @answer int

exec sp_executesql @Str, N'@answer int OUTPUT', @answer OUTPUT

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 02:44:54

[code]INSERT DestinationTable
(
DestinationColumn
)
SELECT SUM(Sundries) +
SUM(Entertainment) +
SUM(BalReimbursement) +
SUM(Fee) +
SUM(TrainerCharges) +
SUM(SeminarPack) +
SUM(Food) +
SUM(Accomodation) +
SUM(Flight)
FROM ExpensesTransactions
WHERE isVerified = 1[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources