Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

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
0
Douglass MacLean
Asked:
Douglass MacLean
2 Solutions
 
LowfatspreadCommented:
either you need to write the "procedure" as a function to split the address into its component address lines outputting as a table... and join to that

or else the split code should be placed directly into the underlying sql...

i don't find your example code very illuminating...

the other way is to execute your stored procedure capturing the output to a temp table
(or writing to the temp table in the sp)  and then join to that ...

you can join to a stored procedure directly... as you're attempting to do...
0
 
Scott PletcherSenior DBACommented:
For best performance, re-write the procedure code as an inline-table-valued function, then use CROSS APPLY to get its results.  For example, something like this:


SELECT
    A.Address2, A.Address2, A.Address3,
    E.City, E.StateProvince, E.PostalCode, E.Country
FROM @Source AS E
CROSS APPLY dbo.upSplitCompoundAddress ( E.EntityId, E.BillingStreet ) AS A
0
 
Douglass MacLeanCTOAuthor Commented:
Thanks to you both. I appreciate the good advice.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now