Link to home
Start Free TrialLog in
Avatar of Douglass MacLean
Douglass MacLeanFlag for United States of America

asked on

Join the output of a stored procedure with a table

I have created a procedure that splits a multi-line street address like
Suite 1
Tower 2
345 Sixth Street
into Address1, Address2, Address3.

The inputs to the procedure are @EntityId and @CompoundAddress

I want to join the result with a temporary table that contains
EntityId, City, StateOrProvince, PostalCode, Country, and the compound address

I want to create a SELECT statement that joins the two tables and presents all the fields

Can someone help me figure out how to do that?

Here is the code I wrote that SQL Server doesn't like:
SET @ThisRow = 14
WHILE @ThisRow <= 14
BEGIN
SET @InAddress = (SELECT BillingStreet FROM @Source WHERE RowNum = @ThisRow)
SELECT A.Address2, A.Address2, A.Address3, E.City, E.StateProvince, E.PostalCode, E.Country FROM
@Source AS E INNER JOIN
(EXEC upSplitCompoundAddress @EntityId, @InAddress) AS A ON A.EntityId = E.EntityId
SET @ThisRow = @ThisRow + 1
END
SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Douglass MacLean

ASKER

Thanks to you both. I appreciate the good advice.