By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,651 Members | 1,551 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,651 IT Pros & Developers. It's quick & easy.

resultset of proc in another proc

P: n/a
Hello,

i want to use the result set from a stored proc in another stored proc, for
example:

create stored procedure proc1 (@x int) as
declare @tbl (y1 int, y2 int)
insert into @tbl values(@ * @x, @x * @x * @x)
select * from @tbl
GO
--
create stored procedure proc2 (@x int) as
declare @tbl (y1 int, y2 int)
while @x > 0 begin
insert into @tbl select (exec proc1 @x) <-- this is my problem
set @x = @x - 1
end
select * from @tbl
GO
--

I know i could use output parameters. But i want to know if something is
possible with SQL-Server?

thanks,
Helmut
Oct 4 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
SQL
Create a temp table in the first proc (2) and populate that table in
the second proc (1)
Like this

create stored procedure proc1 (@x int) as
insert into #temp values(@ * @x, @x * @x * @x)
select * from @tbl
GO
--
create stored procedure proc2 (@x int) as
declare @tbl (y1 int, y2 int)
create #temp (y1 int, y2 int)
while @x > 0 begin
(exec proc1 @x) <-- this is my problem
set @x = @x - 1
end
insert into @tbl
select * from #temp

select * from temp
---or select * from @tbl -- you can eliminate @tbl in this proc
GO
I don't know why you are calling the second proc you can do all this
stuff in 1 proc like this
-------------------------------------------------------------------------------
create stored procedure proc2 (@x int) as
declare @tbl (y1 int, y2 int)
while @x > 0 begin
insert into @tbl
values(@ * @x, @x * @x * @x)
set @x = @x - 1
end
select * from @tbl
GO

http://sqlservercode.blogspot.com/

Oct 4 '05 #2

P: n/a
Am 4 Oct 2005 07:23:52 -0700 schrieb SQL:
Create a temp table in the first proc (2) and populate that table in
the second proc (1)
Like this

create stored procedure proc1 (@x int) as
insert into #temp values(@ * @x, @x * @x * @x)
select * from @tbl
GO


That doesn't help immediately. Because both procs can be called
independently by the client program. But i can put the working part in a
third proc which is called by proc1 or proc2, then i can use your
suggestion.
And i can't put everything in one proc, because in my real application the
first proc should calculate different values for one specific contract, the
second proc should calculate the values for a group of contracts (using
proc1) and present the result in a little different output.

But i am still very interested if it is possible without output parameters
and populated tables.

thanks,
Helmut

Oct 4 '05 #3

P: n/a
helmut woess wrote:
Hello,

i want to use the result set from a stored proc in another stored proc, for
example:

create stored procedure proc1 (@x int) as
declare @tbl (y1 int, y2 int)
insert into @tbl values(@ * @x, @x * @x * @x)
select * from @tbl
GO
--
create stored procedure proc2 (@x int) as
declare @tbl (y1 int, y2 int)
while @x > 0 begin
insert into @tbl select (exec proc1 @x) <-- this is my problem
set @x = @x - 1
end
select * from @tbl
GO
--

I know i could use output parameters. But i want to know if something is
possible with SQL-Server?

thanks,
Helmut


This is a good summary of the options:

http://www.sommarskog.se/share_data.html

Simon
Oct 5 '05 #4

P: n/a
Am Wed, 05 Oct 2005 01:54:33 +0200 schrieb Simon Hayes:
helmut woess wrote:
Hello,

i want to use the result set from a stored proc in another stored proc, for
example:

create stored procedure proc1 (@x int) as
declare @tbl (y1 int, y2 int)
insert into @tbl values(@ * @x, @x * @x * @x)
select * from @tbl
GO
--
create stored procedure proc2 (@x int) as
declare @tbl (y1 int, y2 int)
while @x > 0 begin
insert into @tbl select (exec proc1 @x) <-- this is my problem
set @x = @x - 1
end
select * from @tbl
GO
--

I know i could use output parameters. But i want to know if something is
possible with SQL-Server?

thanks,
Helmut


This is a good summary of the options:

http://www.sommarskog.se/share_data.html

Simon


very good information!

thank you very much,
Helmut
Oct 5 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.