May 13, 2008 at 10:58 am
Hi,
I have an issue where I MUST use a VIEW to retrieve a data set and I can't use a stored procedure because the calling application does not recognize stored procedures. The view needs to contain data from multiple tables from other databases within the same database instance. The logic in a stored procedure goes like this:
1. create a temporary table;
2. insert into the temporary table the contents of another table from another database;
3. repeat step two for multiple tables from other databases;
4. return the contents of the temporary table.
I can do this in a stored procedure but not in a view. So, I'm looking for ideas on how I can perhaps call the stored procedure within the view or include this logic in a view somehow or in a function which will be called from the view.
Bottom line - the view must return a table of data from different locations. Any ideas welcome.
Thanks!
May 13, 2008 at 12:15 pm
The classic, direct way to do this is use four-part names in your From clause for the remote database tables.
Example: Three servers, 1 with the view and the table dbo.Table1, 1 with database DB2 and tables dbo.Table2 and Table3, 1 with databases DB3 (tables 4 and 5) and DB4 (6 and 7).
select Col1, Col2, Col3... (and so on)
from
dbo.Table1
inner join Server2.DB2.dbo.Table2
on Table1.ID = Table2.Table1ID
inner join Server2.DB2.dbo.Table3
on Table2.ID = Table3.Table2ID
inner join Server3.DB4.dbo.Table4
on Table1.CustomerID = Table4.AccountID
inner join Server3.DB4.dbo.Table5
on Table3.OrderID = Table5.OrderID
inner join Server3.... (and so on)
You have to set them up as linked servers first, of course, and have the right security and permissions set up.
Another way to do it is to build a "Multi-Select Table-Valued Function". Build that the same way you would a proc, but use table variables instead of temp tables (can't use temp tables in a function), and select from the function in the view.
The final way that I know of would be to use OpenRowset to execute a proc (local or otherwise) to accomplish this the way you're used to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 13, 2008 at 12:18 pm
Ah - of course! I could probably use a UNION ALL too. Thanks so much. I'm making things more complicated than they need to be.
May 13, 2008 at 12:32 pm
Yes, you can use Union All, or anything else that's normally valid in a view.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply