Ehsan Akbar Ehsan Akbar - 2 months ago 13
SQL Question

Can we make join between two stored procedures in SQL Server

I have this stored procedure:

CREATE PROCEDURE [dbo].[TestPackageAccept]
AS
BEGIN
SELECT A.Id,
a.PackageNumber,
a.Size,
a.Code,
a.TestPackageOrder,
B.status as LineCheckState,
B.ReportNumber as LineCheckReportNumber,
B.SubmitDateTime as LineCheckSubmitDateTime,
c.status as CleaningState,c.ReportNumber as CleanReportNumber,
c.SubmitDateTime as CleanSubmitDateTime,
c.status as ReInstatement,
d.ReportNumber as ReInstatementReportNumber,
d.SubmitDateTime as ReInstatementSubmitDateTime,
E.status as Flushing,
e.ReportNumber as FlushingReportNumber,
e.SubmitDateTime as FlushingSubmitDateTime,
f.status as Drying,
f.ReportNumber as DryingReportNumber,
f.SubmitDateTime as DryingSubmitDateTime,
m.status as PAD,
m.ReportNumber as PADReportNumber,
m.SubmitDateTime as PADSubmitDateTime,
n.status as Mono,
n.ReportNumber as MonoReportNumber,
n.SubmitDateTime as MonoSubmitDateTime,
p.status as Variation,
p.ReportNumber as VariationReportNumber,
p.SubmitDateTime as VariationSubmitDateTime
FROM TestPackages A
outer Apply (Select * from dbo.ReturnAcceptStepOfTestPackage(A.id,'LineCheck')) B
outer Apply (Select * from dbo.ReturnAcceptStepOfTestPackage(A.id,'Clean')) C
outer Apply (Select * from dbo.ReturnAcceptStepOfTestPackage(A.id,'Reinstatment'))D
outer Apply (Select * from dbo.ReturnAcceptStepOfTestPackage(A.id,'Flushing')) E
outer Apply (Select * from dbo.ReturnAcceptStepOfTestPackage(A.id,'Drying')) F
outer Apply (Select * from dbo.ReturnAcceptStepOfTestPackage(A.id,'Test')) G
outer Apply (Select * from dbo.ReturnAcceptStepOfTestPackage(A.id,'PADTest')) M
outer Apply (Select * from dbo.ReturnAcceptStepOfTestPackage(A.id,'Mono')) N
outer Apply (Select * from dbo.ReturnAcceptStepOfTestPackage(A.id,'Variation')) P
END;


And this

CREATE PROCEDURE [dbo].[TestPackageProgress]
AS
BEGIN
SELECT PackageId,
packagenumber,
count(*) as [Total],
sum(case [WeldStatus] when 'Accept' then 1 end) as Accept,
sum(case [WeldStatus] when 'accept' then 0 else 1 end) as Remain,
ROUND(CONVERT(float,sum(case [WeldStatus] when 'Accept' then 1 end))/CONVERT(float,count(*)) * 100,2) as PercentProgress
FROM [SPMS2].[dbo].[JointHistory]
WHERE PackageId is not null
GROUP BY PackageId,PackageNumber;
END;


Can I make a join between these two stored procedure's result sets on
first.id = second.packageid
?

Answer

You can put result sets from both SP into temp tables and then join them:

CREATE TABLE #PackageAccept  (
    Id INT,
    PackageNumber INT,
    Size INT,
    Code NVARCHAR(100),
    TestPackageOrder INT
    --etc 
    --Here you add all columns from SP output with there datatypes
)

Then you can:

INSERT INTO #PackageAccept
EXEC [dbo].[TestPackageAccept]

The same way for second SP, then join:

SELECT *
FROM #PackageAccept pa
INNER JOIN #PackageProgress pp
    ON pa.id = pp.packageid

Don't forget to DROP temp tables:

DROP TABLE #PackageAccept
DROP TABLE #PackageProgress

The full batch will be like:

IF OBJECT_ID(N'#PackageAccept') IS NOT NULL 
BEGIN
    DROP TABLE #PackageAccept
END
ELSE
BEGIN 
    CREATE TABLE #PackageAccept (
        Id INT,
        PackageNumber INT,
        Size INT,
        Code NVARCHAR(100),
        TestPackageOrder INT
        --etc
        )
END


IF OBJECT_ID(N'#PackageProgress') IS NOT NULL 
BEGIN
    DROP TABLE #PackageProgress
END
ELSE
BEGIN 
    CREATE TABLE #P (
        PackageId INT,
        packagenumber INT,
        [Total] INT,
        Accept INT,
        Remain INT
        --etc
        )
END

INSERT INTO #PackageAccept
EXEC [dbo].[TestPackageAccept]

INSERT INTO #PackageProgress
EXEC [dbo].[TestPackageProgress]

SELECT *
FROM #PackageAccept pa
INNER JOIN #PackageProgress pp
    ON pa.id = pp.packageid
Comments