join result of 2 SPs - 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)

join result of 2 SPs

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-11-14 : 08:33:53

Hi,
I have created two stored procedures in sql server 2005.
They both return different data but with the same fields

i.e.

exec uspPricesSourcesBestGet 0,0,0

returns
ID, Field1, Field2, Field3, Field4, Field5

exec uspPricesSourcesBestAllDayGet 0,0,0

returns
ID, Field1, Field2, Field3, Field4, Field5

Now I would like to join these two tables using the ID field and therefore have one table.

I tried having a table variable for the result of each stored procedure but it does not seem to be the way to do what I am after.

This is what I have done so far;

declare @tblPricesSourcesBestLatest table
(
Price_Source_Latest_ID int,
Source_ID smallint,
Bid_Price_Best decimal(12, 8),
Bid_Size_Best decimal(12, 8),
Source_Code_Bid_Collection_Best varchar(1000),
Ask_Price_Best decimal(12, 8)
)

insert into
@tblPricesSourcesBestLatest
exec uspPricesSourcesBestGet 0,0,0

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-14 : 08:36:24

You should use temporary table instead of a table variable.
Also, there are 8 columns in your table while SP returns 6 columns

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 08:40:22

This?

DECLARE	@PricesSourcesBest TABLE
	(
		Price_Source_Latest_ID INT,
		Source_ID SMALLINT,
		Bid_Price_Best DECIMAL(12, 8),
		Bid_Size_Best DECIMAL(12, 8),
		Source_Code_Bid_Collection_Best VARCHAR(1000),
		Ask_Price_Best DECIMAL(12, 8),
		Ask_Size_Best DECIMAL(12, 8),
		Source_Code_Ask_Collection_Best VARCHAR(1000)
	)

INSERT	@PricesSourcesBest
EXEC	uspPricesSourcesBestGet 0, 0, 0

DECLARE	@PricesSourcesBestAll TABLE
	(
		Price_Source_Latest_ID INT,
		Source_ID SMALLINT,
		Bid_Price_Best DECIMAL(12, 8),
		Bid_Size_Best DECIMAL(12, 8),
		Source_Code_Bid_Collection_Best VARCHAR(1000),
		Ask_Price_Best DECIMAL(12, 8),
		Ask_Size_Best DECIMAL(12, 8),
		Source_Code_Ask_Collection_Best VARCHAR(1000)
	)

INSERT	@PricesSourcesBestAll
EXEC	uspPricesSourcesBestAllDayGet 0,0,0

SELECT		*
FROM		@PricesSourcesBest AS b
INNER JOIN	@PricesSourcesBestAll AS a ON a.Source_ID = b.Source_ID

E 12°55'05.25"
N 56°04'39.16"

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-11-14 : 08:50:28

quote:
Originally posted by evilDBA

You should use temporary table instead of a table variable.
Also, there are 8 columns in your table while SP returns 6 columns

This seems to be working now but do not see why I should use a temop table instead.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 08:54:13

The reason is for the number of records returned.
Using temp tables makes the code more predictable in terms of stability.

If you insert to many records (depending on table layout relative to page size) your table variable will need to flush to disk intermittiently. This takes a long time.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources