Executing a Stored Procedure within a Stored Procedure

[andrea96]

I am executing a stored procedure (to calculate a price) within a stored procedure. The "price" stored procedure has an output parameter to return the price to the main stored procedure. However, the main stored procedure is also outputting the results of the "price" stored procedure as "No column name", in addition to its own select statement. This is causing me a problem because I am using the results of this stored procedure to fill a DataSet in VB. The DataSet is creating a table for each separate price and my select statement. For example, I will have 5 "bad" tables before the one I want.

Here is the code for my stored procedure.

ALTER         proc aih_clGetBuildPlan

@KitNo  	varchar(30),
@CustomerCode	varchar(8)

as

DECLARE	@KitNumber	varchar(30),
	@PartNo		varchar(30),
	@Qty		decimal(20, 8),
	@Price		decimal(20, 8)

create table #tmp_clBuildPlan (
	kit_no		varchar(30),
	part_no		varchar(30),
	seq_no		varchar(4),
	qty		decimal(20, 8),
	who_entered	varchar(20),
	description	varchar(255),
	price		decimal(20, 8),
	type		varchar(10)
)

insert into #tmp_clBuildPlan 
(kit_no, part_no, seq_no, qty, who_entered, description, price)
select @KitNo, w.part_no, seq_no, qty, w.who_entered, description, 0.00 
from Millenia60.dbo.what_part w, Millenia60.dbo.inv_master i
where asm_no = @KitNo and
	w.part_no = i.part_no

--If the customer code is passed to this procedure, calculate price
IF @CustomerCode <> ''
BEGIN
    DECLARE price_cursor CURSOR FOR
    SELECT kit_no, part_no, qty from #tmp_clBuildPlan

    OPEN price_cursor

    FETCH NEXT FROM price_cursor
    INTO @KitNumber, @PartNo, @Qty

    WHILE @@FETCH_STATUS = 0
    BEGIN

	EXEC aih_clGetPrice @CustomerCode, @PartNo, @Price OUTPUT
	SET @Price = @Price * @Qty

	UPDATE #tmp_clBuildPlan
	SET price = @Price
	WHERE kit_no = @KitNumber AND
		part_no = @PartNo

	FETCH NEXT FROM price_cursor
	INTO @KitNumber, @PartNo, @Qty

    END

CLOSE price_cursor
DEALLOCATE price_cursor

END

--Flex
update #tmp_clBuildPlan
set type = 'Flex Duct'
where SUBSTRING(part_no, 1, 3) in 
('130', '131', '136', '051', '170', '176', '178', '080', '086', '088')

--Extras
update #tmp_clBuildPlan
set type = 'Extras' 
where part_no in ('33330000', '09900021')

--Components
update #tmp_clBuildPlan
set type = 'Components'
where type is null

select * from #tmp_clBuildPlan

Is there a way I can stop the main stored procedure from returning the price?

Thanks,
Andrea

 

[andrea96]

I figured it out. My called stored procedure was doing an extra Select.