Sybase: Executing a stored procedure in another database

Sometimes you need a stored procedure to be executed in a given database. The reason could be that you use functions like drop trigger or create trigger which do not allow you to specify a database. But if you are currently in the context of another database and need to stay there (e.g. because you need to update proxy databases and need to be in the master database for this), you cannot switch databases by using:

1> use mydbname
2> go

This is not allowed in the scope of a stored procedure.

But what you can do is create a second stored procedure in the other database and call it using the database name:

create procedure mymasterprocedure
as
begin
    ...
    exec myotherdb..myprocinotherdb
    ...
end
go

Note that this also works for procedure which are in the sybsystemprocs database (and are thus callable from any database) i.e. you can call any of these procedures with a database name before the procedure name and execute this procedure in the context of another database e.g.:

1> exec myotherdb..sp_help mytablename
2> go

Leave a Reply

Your email address will not be published.