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

SQL Server Development (2000)

SP Parameter

Author

Topic

elangovan
Starting Member

6 Posts

Posted - 2006-09-15 : 03:30:21

I have written Two SP in sql Server 2000. i am calling the Second Sp inside of First one. I need to send the query output to another Sp from First one. Please Note the Code flow.

Create Proc A
AS
BEGIN
EXEC AA(SELECT * FROM TableName FOR XML AUTO)
END

Create Proc AA
@AA As nText
AS
BEGIN
select
.......
OPENXML().....

END

I can not do this. is it Possible or tell Some solution to implement this kind of things.

Regards
Elango.V

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-15 : 03:35:02

[code]
Create Proc A
AS
BEGIN
CREATE TABLE #TEMP ( . . . ) -- Temp table structure should be same as output of AA
INSERT INTO #TEMP EXEC AA 'SELECT * FROM TableName FOR XML AUTO'
END

Create Proc AA
@AA As nText
AS
BEGIN
select
.......
OPENXML().....

END
[/code]

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-09-15 : 03:36:13

quote:
Originally posted by elangovan

I have written Two SP in sql Server 2000. i am calling the Second Sp inside of First one. I need to send the query output to another Sp from First one. Please Note the Code flow.

Create Proc A
AS
BEGIN
EXEC AA(SELECT * <specific field> FROM TableName FOR XML AUTO)
END

Create Proc AA
@AA As nText
AS
BEGIN
select
.......
OPENXML().....

END

I can not do this. is it Possible or tell Some solution to implement this kind of things.

Regards
Elango.V

Hopes it will work, let us know, if any prob.

Mahesh

elangovan
Starting Member

6 Posts

Posted - 2006-09-15 : 03:37:47

i need to Send query output(without Temp Table) to another SP.

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-09-15 : 03:38:46

quote:
Originally posted by mahesh_bote

quote:
Originally posted by elangovan

I have written Two SP in sql Server 2000. i am calling the Second Sp inside of First one. I need to send the query output to another Sp from First one. Please Note the Code flow.

Create Proc A
AS
BEGIN
EXEC AA(SELECT * <specific field> FROM TableName FOR XML AUTO)
END

Create Proc AA
@AA As nText
AS
BEGIN
select
.......
OPENXML().....

END

I can not do this. is it Possible or tell Some solution to implement this kind of things.

Regards
Elango.V

Hopes it will work, let us know, if any prob.

Mahesh

KHTAN can't we specify specified field name as i have suggested earlier?

Mahesh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-15 : 03:44:22

quote:
Originally posted by elangovan

i need to Send query output(without Temp Table) to another SP.

You need to send query output from sp AA to sp A ?

What's the consideration of not using temp table ?

elangovan
Starting Member

6 Posts

Posted - 2006-09-15 : 03:51:25

Yes. i have written two SPs in Differen DB. it may be Same server or Different Server.
first Sp in Database1 and Second one in Database2.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-15 : 05:05:04

quote:
Originally posted by elangovan

Yes. i have written two SPs in Differen DB. it may be Same server or Different Server.
first Sp in Database1 and Second one in Database2.

If both SPs are on different servers, first thing is to add second server as linked server to first one and change SP to following:

Create Proc A
AS
BEGIN

Select * into #temp from openquery(server2, 'exec db2..ProcA(''SELECT * FROM TableName FOR XML AUTO'')')

Select * from #temp

End

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources