Utilizing two output parameters to run a proc

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

Transact-SQL (2000)

Utilizing two output parameters to run a proc

Author

Topic

gamaz2
Starting Member

31 Posts

Posted - 2008-12-06 : 21:38:59

Hi,
I have a stored procedure which is as follows:

ALTER procedure prr_temp_source_partnum_out1

@part_num1 varchar(20)
,@part_num varchar(20) output
,@projectno varchar(11) output
as
BEGIN
SET NOCOUNT ON
--Make a temporary table
--Make a temporary table that extracts partnumber in one column in accordance with the rule set by Greg

create table #temp1a
(
partnumber varchar(30)
, projectno varchar(11)
)
insert into #temp1a
select
case when overridepart = 0
then tblprrin.partnumberprs
else tblprrin.partnumberprr
end as partnumber
,projectno
from tblprrin

set @projectno = (select projectno
from #temp1a
where partnumber = @part_num1
group by projectno)

select @part_num = @part_num1

END

GO

I am trying to check the value of the output parameters for sample run of the above stored procedure:

declare @part_num varchar(20);

DECLARE @projectno VARCHAR(11);

EXEC prr_temp_source_partnum_out1 '960', @part_num output, @projectno output

select @projectno projectno

select @part_num part_num

In the above the select @projectno projectno outputs a value. However the select @part_num part_num
does not.

I am trying to find why the second statement does not yield the value of 960.

I am then trying to run a different stored procedure utilizing above two values as follows. However

this is not working as I suspect the @part_num value of the first procedure is not coming up.

I appreciate any help to resolve this problem.

THE SECONS STORED PROCEDURE IS EXECUTED AS FOLLOWS:
exec prr_temp_source_out_utilizing_part_num @projectno OUTPUT, @part_num OUTPUT

GO
Please note that the sql statement in the first stored procedure yields the
following:
part_num projectno
E38PLATE 08-01052-02
TTTTTT 05-00316-00
960 05-00316-00
960 05-00316-00
930 05-00315-03
mtt 07-01111-00
GRG 07-01112-00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 02:11:53

you code doesnt look to have any problem. Are you sure that output param @part_num is not returning a value? or can it be a chance that it may be problem with second procedure whose body you havent shown?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources