May 15, 2008 at 3:02 am
Hi,
I'm trying to write a SELECT that gets a list of people from a table in one database and for each row returned, appends related data (as columns from a stored procedure) held in another database (on same server). Is it possible?
I've been trying loads of different permutations of the following theme:
SELECT m.Ref, m.Knownas, m.Lastname, BuildingPasses.dbo.SelectCardUsageSummary(m.CardNumber)
FROM Members m
I was hoping this would return data in the following format:
Ref | Knownas | LastUse | VisitsLast2Weeks
1 | John | Smith | 15/05/2008 | 9
2 | Jane | Black | 14/05/2008 | 8
May 15, 2008 at 3:21 am
It is possible, but not with a stored procedure as you have used in your example. Instead, SelectCardUsageSummary would need to be a function.
Or better still, use a join to the relevant table:
e.g.
SELECT m.Ref, m.Knownas, m.Lastname, b.UsageSummary
FROM Members m
INNER JOIN BuildingPasses.dbo.SomeTable b on b.CardNumber = m.CardNumber
I'm guessing at the columns you'd need to join on, but seeing as you're passing CardNumber to the stored procedure, CardNumber seems like a reasonable assumption.
The only thing to be aware of is cross-database ownership chaining. You need to make sure that your users have the required permissions to access the table in your BuildingPasses database.
Hope that helps,
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply