Left Join 2 stored procedures - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Left Join 2 stored procedures

Author

Topic

bonekrusher
Starting Member

44 Posts

Posted - 2008-10-29 : 10:48:33

Hi, I have 2 stored procedures that return the following

sp1:

Partno
-------
12324
146788

sp2:

Partno
------
12324
134577

How do I do the following:
Query1: a LEFT JOIN
Query2: a NOT IN (for example, Partno found in sp1 and NOT IN sp2)

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-29 : 10:51:44

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

Madhivanan

Failing to plan is Planning to fail

bonekrusher
Starting Member

44 Posts

Posted - 2008-10-29 : 11:09:31

Hi,

OPENROWSET is for including all connection information that is required to access remote data from an OLE DB data source. I need to perform the query from the a T-SQL statement. For this purpose I am using SSME.

Any other ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 11:18:42

use the first approach using two temporay tables like below


CREATE TABLE #Temp1
(
Partno int
)

CREATE TABLE #Temp2
(
Partno int
)

INSERT #Temp1
EXEC sp1....

INSERT #Temp2
EXEC sp2.....

SELECT *
FROM #Temp1 t1
LEFT JOIN #Temp2 t2
on t2.partno=t1.partno

SELECT * 
FROM #Temp1 t1
WHERE t1.Partno NOT IN (SELECT Partno FROM #Temp2)
...

ursangel
Starting Member

17 Posts

Posted - 2008-10-29 : 11:23:03

If both SP's return the same result, and you need to join the 2 means, why cant you go for a Single SP and apply your filter there.

Is bothe the SP's deealing with the same tables? with diff where clause?
Please provide the query and table structure.

Regards
Angel

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 11:24:45

Did you read the first bit of the link?

Is there some reason you can't do a

1)Create temp table with same columns (exactly) as returned by sp
2)Do an INSERT EXEC with a call to that sp.

For example

INSERT #t EXEC <yourSp>

-------------
Charlie

bonekrusher
Starting Member

44 Posts

Posted - 2008-10-29 : 11:43:45

Thanks... How do I drop the table #Temp1 and #Temp2?

When I try:
DROP TABLE #Temp1
I get:
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#Temp1', because it does not exist or you do not have permission.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 11:47:26

Check they exist first

IF OBJECT_ID('tempdb..#temp1') IS NOT NULL DROP TABLE #temp1

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 11:48:09

Or, if you don't have permissions, have a chat with the sql server administrator. Unless that's you?

-------------
Charlie

bonekrusher
Starting Member

44 Posts

Posted - 2008-10-29 : 12:00:42

Thanks...

IF OBJECT_ID('tempdb..#temp1') IS NOT NULL DROP TABLE #temp1

Worked!