Get return values from execute(strSQL)

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)

Get return values from execute(strSQL)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-22 : 07:54:54

leos1981 writes "Create procedure process_transaction
@tableName varchar(20)
AS
declare @vSQL varchar(1000)
select @vSQL = 'select tranId, tranName from ' + @tableName
Execute (@vSQL)
....
process the transaction info
....

How can get the tranId, tranName and other fields value after execute the select statement? It need the information to continue the processing part.

Note: @tableName can be any table."

dev45
Yak Posting Veteran

54 Posts

Posted - 2005-03-22 : 08:14:30

it may not be the best solution, but it's an idea
u can have a table (or create a temporary table) let's say : theTable and alter the @vSQL as follows :
select @vSQL = 'truncate table theTable; insert into theTable(tranId, tranName) select tranId, tranName from ' + @tableName
execute(@vsql)
then u can retrieve the values from theTable
(take a look at the sp_executeSql stored proc)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-22 : 08:15:15

use a temp table
create #temp (tranId int, tranName varchar(8000))
insert into #temp (tranId, tranName)
Execute (@vSQL)

select * from #temp

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

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-03-22 : 10:27:29

You can also use:

DECLARE @return INT
EXEC @return = sp_executesql @sql
IF @return <> 0 blah, blah, blah

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-03-22 : 10:29:42

:) I totally missed the boat on this one didn't I. lol This wil give you the return value so you can have error handling.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-22 : 10:37:44

admit it derrick! you just did it for the post count