Nested Query Help Needed

[ckelsoe]

Hi, I have the following scenario that I am not sure how to best tackle. Any
advice or examples is appreciated.

I am creating a stored proc that requires a code to be passed to it. In
return data gathered from 3 different tables will be returned. The big catch
is that 1 of the tables resided in a differenct database.

So, here is the data layout.

Database 1, Table 1 contains the following fields: Job, CustID, ShipID, and
ShipMethod.

Database 2, Table 1 contains CustID, ShipID, Address, City, State, Zip,
etc...

Database 2, Table 2 contains CustID, CustomerName.

So the first question is how should the stored proc look with an input
parameter of "Job" and output of Job, CustID, ShipMethod, ShipID, Address,
City, State, Zip, and CustomerName?

Secondly, which database should the stored proc reside?

Again, any advise, suggestions, pointers, etc. are appreciated.

 

[xcata]

Hi,

Your stored procedure may reside in no mather database if every time you specify the tables you include the name of the database. As example:

create procedure test
@job varchar(50)
as
select Job = a.Job,
CustId = a.CustId,
ShipMethod = a.ShipMethod
Name = b.CustomerName,
Adress = c.Address,
City = c.City,
State = c.State
from database_1..Table_1 a
inner join database_2..Table_2 b on
a.CustID = b.CustID
inner join database_2..Table_1 c on
a.CustID = c.CustID and
a.ShipId = c.ShipId

 

[ckelsoe]

Thanks. I figured that out. I was overcomplicating the problem when a well designed sql statement like yours was the solution. I appreciate the help.