Could Stored Procedure or Trigger choose another database?

Hello all, I need some advice or clue here.
I want to crate a stored procedure or trigger from database A that will
change of have effect on another database B. Is this could be done? I
try to use sql syntax "use B;" in stored procedure that created on
database A, but it said that use syntax couldn't be used in stored
procedure."Lemune" <alfredosilitonga@.gmail.com> wrote in message
news:1147850181.634882.15120@.i39g2000cwa.googlegroups.com...
> Hello all, I need some advice or clue here.
> I want to crate a stored procedure or trigger from database A that will
> change of have effect on another database B. Is this could be done? I
> try to use sql syntax "use B;" in stored procedure that created on
> database A, but it said that use syntax couldn't be used in stored
> procedure.
>
Do you mean something like
create procedure FOO as
(
select * from bar
)
where bar is in a different database?
If so, 3-part names
select * from dbname.owner.table
so
create procedure FOO as
(
select * from baz.dbo.bar
)|||Lemune,
yes you can change the datbase context inside a stored procedure even
thought the "Use" keyword is not allowed.
One way is creating a string variable and build up the syntax you need,
when execute it through sp_executesql.
Declare @.myQuery nvarchar(2000)
set @.myQuery = ' USE ' + @.dbname + CHAR(13) + 'SELECT * from myTable'
Exec sp_executesql @.myQuery
Another option is symply using the 3 part name:
Select * from myDb.dbo.myTable
If you try to loop through all datbases on a server have a look at
sp_MSforeachDB. It gives you an easy way to run the same command on all
databases.
Markus|||Thanks for your information and MarkusB too, I try booth your method
and it work well.
But I have another problem, assume that I want to create stored
procedure that work from A.dbo.Incomming to check a value from
B.dbo.Data and than the result value is send to A.dbo.Outgoing in one
stored procedure. I'm using MS Sql Server 7. I also have read that in
MS SQL 2003 have stored functions that return value.|||I have another question, how stored procedure in SQL Server 7 could
return value (like function)?|||Inside the proc, you do:
RETURN -101
And the calling code, of TSQL:
DECLARE @.retval int
EXEC @.retval = myproc
Return codes are int only and should be used to communicate status (success
or fail). You can also
return values through output parameters:
CREATE PROC myproc @.parm varchar(5) OUT
AS
SET @.parm = 'Hello'
GO
DECLARE @.x varchar(5)
EXEC myproc @.parm = @.x OUT
Both return codes and out parameters can both be retrieved from your program
ming API (ADO.NET, for
example) as well.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lemune" <alfredosilitonga@.gmail.com> wrote in message
news:1147948815.225334.130080@.i40g2000cwc.googlegroups.com...
>I have another question, how stored procedure in SQL Server 7 could
> return value (like function)?
>|||Thank you very much.|||I have another question again :-). Could Stored Procedure or Trigger
choose another database that is on difference engine? For example, I
have database A on engine or instance name Server1 that has a stored
procedure Stored_Proc_1 and in this stored procedure I want to access
some table on database B on engine Server2. I have username and
password on Server2.

Could Stored Procedure or Trigger choose another database?

Hello all, I need some advice or clue here.
I want to crate a stored procedure or trigger from database A that will
change of have effect on another database B. Is this could be done? I
try to use sql syntax "use B;" in stored procedure that created on
database A, but it said that use syntax couldn't be used in stored
procedure."Lemune" <alfredosilitonga@.gmail.com> wrote in message
news:1147850181.634882.15120@.i39g2000cwa.googlegroups.com...
> Hello all, I need some advice or clue here.
> I want to crate a stored procedure or trigger from database A that will
> change of have effect on another database B. Is this could be done? I
> try to use sql syntax "use B;" in stored procedure that created on
> database A, but it said that use syntax couldn't be used in stored
> procedure.
>
Do you mean something like
create procedure FOO as
(
select * from bar
)
where bar is in a different database?
If so, 3-part names
select * from dbname.owner.table
so
create procedure FOO as
(
select * from baz.dbo.bar
)|||Lemune,
yes you can change the datbase context inside a stored procedure even
thought the "Use" keyword is not allowed.
One way is creating a string variable and build up the syntax you need,
when execute it through sp_executesql.
Declare @.myQuery nvarchar(2000)
set @.myQuery = ' USE ' + @.dbname + CHAR(13) + 'SELECT * from myTable'
Exec sp_executesql @.myQuery
Another option is symply using the 3 part name:
Select * from myDb.dbo.myTable
If you try to loop through all datbases on a server have a look at
sp_MSforeachDB. It gives you an easy way to run the same command on all
databases.
Markus|||Thanks for your information and MarkusB too, I try booth your method
and it work well.
But I have another problem, assume that I want to create stored
procedure that work from A.dbo.Incomming to check a value from
B.dbo.Data and than the result value is send to A.dbo.Outgoing in one
stored procedure. I'm using MS Sql Server 7. I also have read that in
MS SQL 2003 have stored functions that return value.|||I have another question, how stored procedure in SQL Server 7 could
return value (like function)?|||Inside the proc, you do:
RETURN -101
And the calling code, of TSQL:
DECLARE @.retval int
EXEC @.retval = myproc
Return codes are int only and should be used to communicate status (success or fail). You can also
return values through output parameters:
CREATE PROC myproc @.parm varchar(5) OUT
AS
SET @.parm = 'Hello'
GO
DECLARE @.x varchar(5)
EXEC myproc @.parm = @.x OUT
Both return codes and out parameters can both be retrieved from your programming API (ADO.NET, for
example) as well.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lemune" <alfredosilitonga@.gmail.com> wrote in message
news:1147948815.225334.130080@.i40g2000cwc.googlegroups.com...
>I have another question, how stored procedure in SQL Server 7 could
> return value (like function)?
>|||Thank you very much.|||I have another question again :-). Could Stored Procedure or Trigger
choose another database that is on difference engine? For example, I
have database A on engine or instance name Server1 that has a stored
procedure Stored_Proc_1 and in this stored procedure I want to access
some table on database B on engine Server2. I have username and
password on Server2.