temp tables in nested sproc
-
I'm using a sproc that calls another sproc with an output param and populates the temp table 1 in sproc 1 from sproc 2 output param.
When I exec sproc 1 I get the results of both tables where as I only want to see the results of the temp table in sproc 1.
is there a way I can get around this:
here's the sproc:
-------------------------------------
if exists (select * from sysobjects where name = N'sp_Fleet1')
drop procedure sp_Fleet1
go
CREATE PROCEDURE sp_Fleet1(@country varchar(50), @team varchar(50))
AS
SET NOCOUNT ON
-- declare all variables!
DECLARE @iReturnCode int,
@iNextRowId int,
@iCurrentRowId int,
@iLoopControl int,
@Vslname varchar(50),
@vsl_sii decimal(4,3)
select @country = 'Vancouver'
select @team = 'Phoenix'
-- Initialize variables!
SELECT @iLoopControl = 1
SELECT @iNextRowId = MIN(tk.ID)
FROM tkSiteId tk, vfsif001 vf
where tk.SiteId = vf.SiteId
and tk.managed_by_country = @country
and tk.team = @team
-- Make sure the table has data.
IF ISNULL(@iNextRowId,0) = 0
BEGIN
SELECT 'No data in table!'
RETURN
END
--Create temp table to store values
create table #fleet_vsls
(vsl_name varchar(50),
vslsii decimal(4,3),
country varchar(50),
team varchar(50)
 
-- Retrieve the first row
SELECT @iCurrentRowId = tk.Id,
@Country = Managed_by_Country,
@Vslname = vslname
FROM tkSiteId tk, vfsif001 vf
where tk.SiteId = vf.SiteId
and tk.id = @iNextRowId
-- start the main processing loop.
WHILE @iLoopControl = 1
BEGIN
-- call the sp_vessel sproc to calculate the vessel sii
exec sp_vessel @vessel = @vslname, @vslsii = @vsl_sii output
INSERT INTO #fleet_vsls
VALUES (@vslname,
@vsl_sii,
@country,
@team
 
-- Reset looping variables.
SELECT @iNextRowId = NULL
-- get the next id
SELECT @iNextRowId = MIN(tk.Id)
FROM tkSiteId tk, vfsif001 vf
where tk.SiteId = vf.SiteId
AND tk.Id > @iCurrentRowId
-- are there any more rows?
IF ISNULL(@iNextRowId,0) = 0
BEGIN
select * from #fleet_vsls
BREAK
END
-- get the next row.
SELECT @iCurrentRowId = tk.Id,
@Country = Managed_by_Country,
@vslname = vslname
FROM tkSiteId tk, vfsif001 vf
where tk.SiteId = vf.SiteId
AND tk.Id = @iNextRowId
--
select * from #fleet_vsls
end
go
grant execute on sp_fleet1 to public
go
exec sp_fleet1 @country = '@country', @team = '@team'
--RETURN
-
This was removed by the editor as SPAM
-
For someone to be able to help you should provide the tables and sample data with the req result.
From my undertanding (which can be wrong ) if you transform your SP sp_vessel into a UDF your SP can be reduced to
SELECT vslname, dbo.sp_vessel(vslname),@country, @team
FROM tkSiteId tk, vfsif001 vf
WHERE tk.SiteId = vf.SiteId
AND tk.managed_by_country = @country
AND tk.team = @team
Kindest Regards,Vasc
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply