 IP
|
 |
| Joined: 12 Apr 2005 |
| Total Posts: 47 |
| |
|
Adding results of called stored proc. which returns value as row
Posted: 23 Feb 2006 08:36 PM |
I am calling one stored procedure from another
There are no output parameters in test2 procedure
1st proc
-------------------------------
create proc test1 as
declare @TotalSell float
exec @TotalSell= test2 '1,'2','3'
select @TotalSell=@TotalSell+100
print @TotalSell
------------------------------------------------
2nd Proc
-----------------------------------------
create proc test2 (
@p1 Int,
@p2 real,
@p3 Int
)
AS
SET @price = @p1+@p2+@p3
/* return total price */
SELECT @price AS [Total Price]
---------------------------------------------------
2nd proc gives result '6' as row but not as output parameter.
i want the result of proc test1 as 100+6=106.
But now it is returning as only 100.
It is not adding 6 into 100. The reason is test2 is returning row and not value.
I can't change the proc test2.
I can change only proc test1.
Can you please provide any solution?
Thanks,
IP
|
|
|
 |
|
|
Re: Adding results of called stored proc. which returns value as row
Posted: 24 Feb 2006 08:58 AM |
Best solution would be to change the test2 procedure, but as you can't do that, try this quick work around.
create proc test1 as
declare @TotalSell float
create table #t ( pr int )
insert into #t
exec test2 '1','2','3'
SELECT @totalsell = pr FROM #t
select @TotalSell=@TotalSell+100
print @TotalSell
GO
|
Thanks
Phill
Colt 45 - the original point and click interface ;)
http://philcart.blogspot.com/ |
|
|
|
IP
|
|
| Joined: 12 Apr 2005 |
| Total Posts: 47 |
| |
|
Re: Adding results of called stored proc. which returns value as row
Posted: 24 Feb 2006 01:30 PM |
Hi Philip,
Your solution is correct but I don't want to use this solution as this will not give less response time for executing. I want to call this proc for more than 10000 times in one shot.
Can you please suggest me any other solution?
Thanks,
IP
|
|
|
|
|
|
Re: Adding results of called stored proc. which returns value as row
Posted: 24 Feb 2006 01:38 PM |
| Have you tried using a table variable instead of a temporary table? That should be much quicker. |
|
|
|
|
IP
|
|
| Joined: 12 Apr 2005 |
| Total Posts: 47 |
| |
|
Re: Adding results of called stored proc. which returns value as row
Posted: 24 Feb 2006 01:43 PM |
I have tried this but I think table variable can't be created with procedures.
May be I am not aware how to create table varibale with proc.
Can you please write the query for table variable. |
|
|
|
|
|
Re: Adding results of called stored proc. which returns value as row
Posted: 24 Feb 2006 02:03 PM |
Creating a table variable is much the same as creating a temp table,
instead of
CREATE TABLE #t (pr int)
you have
DECLARE @t TABLE (pr int)
|
Thanks
Phill
Colt 45 - the original point and click interface ;)
http://philcart.blogspot.com/ |
|
|
|
|
Re: Adding results of called stored proc. which returns value as row
Posted: 24 Feb 2006 02:06 PM |
Just like this:
create proc test1 as
declare @TotalSell float
declare @t table ( pr int )
insert into @t
exec test2 '1','2','3'
SELECT @totalsell = pr FROM @t
select @TotalSell=@TotalSell+100
print @TotalSell
The only change is the 'declare' instead of 'create'.
Oh, it's worth noting that pre-SQL05, you couldn't insert the results of a stored procedure into a table variable.
And I'm not sure about whether it would actually be faster this way. Worth doing a couple of tests.
Rob |
|
|
|
|
IP
|
|
| Joined: 12 Apr 2005 |
| Total Posts: 47 |
| |
|
Re: Adding results of called stored proc. which returns value as row
Posted: 24 Feb 2006 03:52 PM |
Thanks
IP |
|
|
|
|
IP
|
|
| Joined: 12 Apr 2005 |
| Total Posts: 47 |
| |
|
Re: Adding results of called stored proc. which returns value as row
Posted: 24 Feb 2006 03:54 PM |
Is there anything like
select @TotalSell=[Total Sell] from ( exec test2 '1','2','3' ) a
|
|
|
|
|
|
Re: Adding results of called stored proc. which returns value as row
Posted: 24 Feb 2006 03:58 PM |
| Would be nice, but no, unfortunately not. |
|
|
|
|