Solved

Join the output of a stored procedure with a table

Posted on 2013-11-29
3
343 Views
Last Modified: 2013-11-29
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
Comment
Question by:dmaclean2
3 Comments
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 200 total points
ID: 39685309
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 300 total points
ID: 39686191
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
 

Author Closing Comment

by:dmaclean2
ID: 39686371
Thanks to you both. I appreciate the good advice.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now