find right database for stored procedure

I have a message queueing system set up so that applications can insert
message information into a database using a stored procedure in the master
database...
CREATE PROCEDURE sp_PutInQueue
<...message parameters...>
AS
DECLARE @.dbid smallint
--get the database context who is running this procedure
SET @.dbid = db_id()
INSERT INTO MessageQueue.Queue (
<...message parameter fields...>,
dbID)
VALUES (
<...message parameters...>,
@.dbid)
GO
After I process the message, I want to use the stored dbID in the table to
reference the calling application and execute a stored procedure in that
database so it will know it's message has been processed. Since I can't use
the USE clause in a stored procedure, how can I accomplish what I am trying
to do'--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Use dynamic SQL & a scope:
-- start in master db
declare @.sql varchar(200)
declare @.dbid varchar(30)
set @.dbid = 'pubs'
set @.sql = 'use ' + @.dbid + ' select top 2 * from authors'
-- or for an SP: set @.sql = 'exec ' + @.dbid + '..MySp param1, param2'
execute (@.sql)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQksjIoechKqOuFEgEQKqCwCghzNcxXMIEnfQ
pw55MCLSObS1Ld8Anit2
R+yW+XNslP/Rm6wTVEBmA243
=MqHA
--END PGP SIGNATURE--
Random wrote:
> I have a message queueing system set up so that applications can insert
> message information into a database using a stored procedure in the master
> database...
> CREATE PROCEDURE sp_PutInQueue
> <...message parameters...>
> AS
> DECLARE @.dbid smallint
> --get the database context who is running this procedure
> SET @.dbid = db_id()
> INSERT INTO MessageQueue.Queue (
> <...message parameter fields...>,
> dbID)
> VALUES (
> <...message parameters...>,
> @.dbid)
> GO
> After I process the message, I want to use the stored dbID in the table to
> reference the calling application and execute a stored procedure in that
> database so it will know it's message has been processed. Since I can't u
se
> the USE clause in a stored procedure, how can I accomplish what I am tryin
g
> to do'