Need help in the following stored proc. manipulati

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)

Need help in the following stored proc. manipulati

Author

Topic

gamaz2
Starting Member

31 Posts

Posted - 2008-12-14 : 02:03:20

Hi,
I have a stored procedure as follows:
alter procedure prr_temp_source_partnum_out

@part_num varchar(50)
--,@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
(
projectno varchar(11),
partnumber varchar(50)
)
insert into #temp1a
select

projectno,
case when overridepart = 0
then tblprrin.partnumberprs
else tblprrin.partnumberprr
end as partnumber

from tblprrin
select * from #temp1a
where partnumber = @part_num
/*
set @projectno = (select projectno
from #temp1a
where partnumber = @part_num
group by projectno)
select @part_num
*/

END

GO
Now when I pass the part_num 'E360MLID' to the stored procedure I get the following output
projectno partno
08-03520-00 E360MLID
08-03267-01 E360MLID
06-10595-03 E360MLID

Now I need to utilize this data set to another stored procedure which is as follows:

CREATE procedure prr_temp_source_out_utilizing_part_num

@project_num varchar(11),
-- ,@plant varchar(11)
@part_num varchar(30)
as
BEGIN
SET NOCOUNT ON
--Make a temporary table
create table #temp1
(
project_num varchar(11)
, project_desc ntext
, eng_id varchar(11)
, last_name varchar(30)
, first_name varchar(30)
, tc_num smallint
, tc_loc varchar(30)
, plantid varchar(11)
)
insert into #temp1
SELECT

PRS.dbo.Project.Project_Num
, PRS.dbo.Project.Project_Desc
, PRS.dbo.Project.Eng_ID
, PRS.dbo.Users.Last_Name
, PRS.dbo.Users.First_Name
, PRS.dbo.Users.TC_Num
, PRS.dbo.Tech_Centers.TC_Loc
, PRS.dbo.tblCostPlants.PlantID
FROM PRS.dbo.Project INNER JOIN
PRS.dbo.Users ON PRS.dbo.Project.Eng_ID = PRS.dbo.Users.User_ID INNER JOIN
PRS.dbo.Tech_Centers ON PRS.dbo.Users.TC_Num = PRS.dbo.Tech_Centers.TC_Num LEFT JOIN
PRS.dbo.tblCostPlants ON PRS.dbo.Project.Project_Num = PRS.dbo.tblCostPlants.Project_num
--select * into temp1 from #temp1
--select * from #temp1

create table #temp2
(

project_num varchar(11)
, project_num_trun varchar(8)
, project_desc ntext
, eng_id varchar(11)
, last_name varchar(30)
, first_name varchar(30)
, tc_num smallint
, tc_loc varchar(30)
, plantid varchar(11)
, plant_desc varchar(20)
)

-- select * from #temp2
insert into #temp2
SELECT

Project_Num
, (Left([project_num],8)) AS project_num_trun
, Project_Desc
, Eng_ID
, Last_Name
, First_Name
, TC_Num
, TC_Loc
, PlantID
, Plant_Desc
FROM dbo.#temp1 LEFT JOIN
PRS.dbo.Plants ON dbo.#temp1.PlantID = PRS.dbo.Plants.Plant
--where #temp2.project_num = @project_num

--select * from #temp2
--where project_num = @project_num

create table #temp3
(

project_num varchar(11)
,project_desc ntext
,part_num varchar(20)
,engineer varchar(60)
,plant varchar(20)
)

insert into #temp3

SELECT

dbo.#temp2.Project_Num
--, project_num_trun
, Project_Desc
, PRS.dbo.tblSoldInfo.part_num
--, Eng_ID
--, Last_Name
--, First_Name
, Engineer = (last_name + ',' + first_name)
--, TC_Num
--, TC_Loc
--, PlantID
, plant = Plant_Desc
FROM dbo.#temp2 LEFT JOIN
PRS.dbo.tblSoldInfo ON dbo.#temp2.project_num_trun = PRS.dbo.tblSoldInfo.project_num
where dbo.#temp2.project_num = @project_num

-- select * from #temp3
--where project_num = @project_num
--where plant = @plant

--select overridepart from tblprrin where projectno = @project_num
create table #temp4
(
prr_id int,
project_num varchar(11)
,project_desc ntext
,engineer varchar(60)
,plant varchar(20)
,part_num varchar(20)
,prr_in_added_date datetime
,poduedate datetime
)
insert into #temp4
select
tblprrin.prr_id,
#temp3.project_num
,#temp3.project_desc
,#temp3.engineer
,#temp3.plant
,case when overridepart = 0
then tblprrin.partnumberprs
else tblprrin.partnumberprr
end as partnumber
,tblprrin.prr_in_added_date
,tblprrin.poduedate

from #temp3 inner join
tblprrin on tblprrin.projectno = #temp3.project_num and
--tblprrin.plantprs = #temp3.plant
(tblprrin.plantprs = #temp3.plant OR #temp3.plant IS NULL)

where tblprrin.partnumberprs is not null

select * from #temp4
where part_num = @part_num
--where plant =@plant

/*
select
tblPrrIn.prr_id
, tblPrrIn.needupdate
, #temp4.project_num
, #temp4.project_desc
, #temp4.part_num
, #temp4.engineer
, #temp4.plant
, #temp4.prr_in_added_date
, #temp4.poduedate
from tblPrrIn inner join
#temp4 On tblPrrIn.projectno = #temp4.project_num
WHERE tblPrrIn.Prr_In_added_date = #temp4.Prr_In_added_date

*/

END
The thing is I need to use each of the rows (obtained from the first stored procedure result) as parameters to the second stored procedure. If I use each of this row as parameter separately I get three separate output. However I need get the three output as a dataset (instead of three separate data set) . I am not getting much clue as how to achive this. Any help is highly appreciated. Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-14 : 12:31:33

isnt this same as
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=116249

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources