temp table on 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)

temp table on stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-30 : 09:22:57

Shams writes "Hi all,

I have a stored procedure (let's call SP_A) that creates a temporary table to store the resultant rows and pass them back.

Now I am creating a job (another stored procedure, say SP_B) that would run periodically and that would be calling SP_A with different parameters.

I am getting back the following error when I run SP_B:

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#tempTable'.

I realize that these temporary tables are scoped for the stored procedure, but wouldn't I still be able to return the data to the calling procedure?

Thanks
Shams"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-30 : 09:31:19

in sp_A you need

select * from #tempTable

then call this from sp_B with

insert #mytbl
exec sp_A

you can then access the data in #mytbl.

#tempTable will be dropped when sp_A completes and not be accessible from sp_B

Another option is to create #tempTable in sp_B, populate it in sp_A then you can access the data in sp_B.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources