Remaining in table context for execute statement

[yumbelie]

Hi,
Is there any way I can remain in context during an update to another table? The problem I'm having is that if I capture the current table I'm in to a variable, and then perform a USE statement, I immediatly leave the context of my current table for the new one. Now, if I do an EXECUTE statement - such as EXECUTE ('USE '+@dbnameIcaptured) - the db switches back, but only for the context of the execute statement. What I really want to be able to do is perform a 'batch' switch, e.g - switch from the current table to master table, perfrom some delete operations, and swtich back - but the EXECUTE command only takes a single statement, and chaining multiple statements together in sp_executesql doesn't help either, as it treats each uniquely. I'm at a loss here, can someone suggest I can perform this operation.

thanks

Yum.

 

[dbomrrsm]

rather than switching beteen databases can you not qualify the db on the one statement

delete from db1.table1 where ....
GO
update db2.table2
set db2.table2.field1 = 'hope the switch worked'
GO
delete from db1.table1 where ....

[blue]DBomrrsm[/blue]

 

[yumbelie]

no, it's the dropping of an extended stored procedure in the master database. the DROP PROCEDURE does not allow a database to be qualified, but does not work unless the selected DB is MASTER.

Any ideas?

 

[JamesLean]

You could do the whole drop within the EXEC:

EXEC('USE master DROP PROC proc_name')

--James

 

[yumbelie]

thanks, thats sorted it