Joining Sp output with a table

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 2000 Forums

SQL Server Development (2000)

Joining Sp output with a table

Author

Topic

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-10-03 : 23:48:15

Hi,

Could someone tell me how can I join a table with the output od a Sp : SQL Server 2000

Thanx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-04 : 02:28:57

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 02:56:08

In addition to method suggested in link, you can use a temporary table and dump resultset of SP into it

INSERT #Temp
EXEC Spname params....

then use it to join to yourtable

SELECT *
FROM YourTable t
JOIN #Temp tmp
ON tmp.fields=t.fields....

you should make sure that the structure of temporary table matches the resultset of SP.

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-10-06 : 06:54:02

Hi Visakh16,

I appreciate your reply. However there is one problem and that is the Sp expects one parameter (itemcode) and also returns a result set that has the itemcode among other information. The itemcode is also there in the join condition.

Table1
itemcode item
123 xyz
345 abc

I want to join table1 with the result of the Sp where itemcode of table1 matches the sp output.

Thanx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 07:06:33

ok so for every records of table you want sp to return results based on one of column value as parameter? is that what you want?
in such case its best to create a udf rather than sp. udfs have more usability compared to sps as they can be used flexibly on both select statements and joins.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 08:32:02

What you need seems to be SQL Server 2005 and CROSS APPLY.

E 12°55'05.63"
N 56°04'39.26"

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-10-06 : 09:11:59

Visakh16,

You have got the point. I haven't created a udf in Sql Server. Looking for some assistance.

Thanx

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-10-06 : 09:20:20

Visakh16,

You have got the point. I have not created an Udf in Sql Server though have done so in VFP. Looking for assistance.

Thanx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 09:32:52

quote:
Originally posted by mayoorsubbu

Visakh16,

You have got the point. I have not created an Udf in Sql Server though have done so in VFP. Looking for assistance.

Thanx

http://www.sqlteam.com/article/user-defined-functions

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-10-07 : 09:39:25

Thanx Visakh16

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources