Combine multiple procedures resultsets

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 2008 Forums

Transact-SQL (2008)

Combine multiple procedures resultsets

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-03-01 : 08:09:33

I have multiple conditions wise stored procedures and I want to combine result sets into single procedure...


ALTER PROCEDURE [dbo].[usp_TotalReceivedUnits_Country]
@ContractCode NVARCHAR (20), 
@CountryCode NVARCHAR (10),
@Years NVARCHAR(1000)
AS
BEGIN
        SELECT ISNULL(SUM(S.NettoWeight), 0) TotalNettoWeight, COUNT(S.ShipmentID) Loads, ReceivalDateYear = YEAR(S.ReceivalDate)
        FROM   Shipment S
        WHERE S.ContractCode = @ContractCode AND 
              S.[OrigCountryCode] = @CountryCode AND
              S.ClosedYN = 0 AND 
              S.Shipped = 1 AND
              S.DeletedYN = 0 AND
              S.OutInbound = 2 AND
              YEAR(S.ReceivalDate) IN (SELECT Data FROM dbo.fnSplitString(@Years, ','))
        GROUP BY YEAR(S.ReceivalDate)
END
GO

ALTER PROCEDURE [dbo].[usp_TotalProcessedUnits_Country]
@ContractCode NVARCHAR (20), 
@CountryCode NVARCHAR (10),
@Years NVARCHAR(1000)
AS
BEGIN
    SELECT ISNULL(SUM(S.NettoWeight), 0) TotalNettoWeight, COUNT(S.ShipmentID) Loads, ReceivalDateYear = YEAR(S.ReceivalDate)
    FROM   Shipment S with (nolock)
    WHERE S.ContractCode = @ContractCode AND 
          S.OrigCountryCode = @CountryCode AND
          S.ClosedYN = 1 AND 
          S.Shipped = 1 AND
          S.FinanceDetailsYN = 0 AND
          S.DeletedYN = 0 AND
          S.OutInbound = 2 AND
          YEAR(S.ReceivalDate) IN (SELECT Data FROM dbo.fnSplitString(@Years, ','))
    GROUP BY YEAR(S.ReceivalDate)
END
GO

I have multiple conditions wise stored procedures and I want to combine result sets into single procedure...

ALTER PROCEDURE [dbo].[usp_TotalReceivedUnits_Country]
@ContractCode NVARCHAR (20),
@CountryCode NVARCHAR (10),
@Years NVARCHAR(1000)
AS
BEGIN
SELECT ISNULL(SUM(S.NettoWeight), 0) TotalNettoWeight, COUNT(S.ShipmentID) Loads, ReceivalDateYear = YEAR(S.ReceivalDate)
FROM Shipment S
WHERE S.ContractCode = @ContractCode AND
S.[OrigCountryCode] = @CountryCode AND
S.ClosedYN = 0 AND
S.Shipped = 1 AND
S.DeletedYN = 0 AND
S.OutInbound = 2 AND
YEAR(S.ReceivalDate) IN (SELECT Data FROM dbo.fnSplitString(@Years, ','))
GROUP BY YEAR(S.ReceivalDate)
END
GO

ALTER PROCEDURE [dbo].[usp_TotalProcessedUnits_Country]
@ContractCode NVARCHAR (20),
@CountryCode NVARCHAR (10),
@Years NVARCHAR(1000)
AS
BEGIN
SELECT ISNULL(SUM(S.NettoWeight), 0) TotalNettoWeight, COUNT(S.ShipmentID) Loads, ReceivalDateYear = YEAR(S.ReceivalDate)
FROM Shipment S with (nolock)
WHERE S.ContractCode = @ContractCode AND
S.OrigCountryCode = @CountryCode AND
S.ClosedYN = 1 AND
S.Shipped = 1 AND
S.FinanceDetailsYN = 0 AND
S.DeletedYN = 0 AND
S.OutInbound = 2 AND
YEAR(S.ReceivalDate) IN (SELECT Data FROM dbo.fnSplitString(@Years, ','))
GROUP BY YEAR(S.ReceivalDate)
END
GO

Above two procedures have same result counts by processed and received summary but How can I combine them to one query with multiple conditions?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-01 : 08:16:01

First glance:

select ...
from ...
where ...
group by ...

UNION ALL

select ...
from ...
where ...
group by ...

So the 2 selects will give 1 result set

Too old to Rock'n'Roll too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-01 : 08:21:54

[code]
ALTER PROCEDURE [dbo].[usp_Total_Country]
@ContractCode NVARCHAR (20),
@CountryCode NVARCHAR (10),
@Years NVARCHAR(1000)
AS
BEGIN
SELECT ISNULL(SUM(CASE WHEN S.ClosedYN = 1 AND S.FinanceDetailsYN = 0 THEN S.NettoWeight ELSE 0 END), 0) TotalNettoWeightProcessed,
COUNT(CASE WHEN S.ClosedYN = 1 AND S.FinanceDetailsYN = 0 THEN S.ShipmentID ELSE NULL END) LoadsProcessed,
ISNULL(SUM(CASE WHEN S.ClosedYN = 0 THEN S.NettoWeight THEN 0 END), 0) TotalNettoWeightreceived,
COUNT(CASE WHEN S.ClosedYN = 0 THEN S.ShipmentID ELSE NULL END) LoadsReceived,
ReceivalDateYear = YEAR(S.ReceivalDate)
FROM Shipment S with (nolock)
WHERE S.ContractCode = @ContractCode AND
S.OrigCountryCode = @CountryCode AND
S.Shipped = 1 AND
S.OutInbound = 2 AND
S.DeletedYN = 0 AND
YEAR(S.ReceivalDate) IN (SELECT Data FROM dbo.fnSplitString(@Years, ','))
GROUP BY YEAR(S.ReceivalDate)
END
GO
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-01 : 08:24:31

SELECT ISNULL(SUM(CASE WHEN S.ClosedYN = 0 THEN S.NettoWeight ELSE 0 END), 0) TotalNettoWeightReceived,
ISNULL(SUM(CASE WHEN S.ClosedYN = 1 AND S.FinanceDetailsYN = 0 THEN S.NettoWeight ELSE 0 END), 0) TotalNettoWeightProcessed,
COUNT(CASE WHEN S.ClosedYN = 0 THEN S.ShipmentID ELSE 0 END ) LoadsReceived,
COUNT(CASE WHEN S.ClosedYN = 1 AND S.FinanceDetailsYN = 0 THEN S.ShipmentID ELSE 0 END) LoadsProcessed,
ReceivalDateYear = YEAR(S.ReceivalDate)
FROM Shipment S
WHERE S.ContractCode = @ContractCode AND
S.[OrigCountryCode] = @CountryCode AND
S.Shipped = 1 AND
S.DeletedYN = 0 AND
S.OutInbound = 2 AND
YEAR(S.ReceivalDate) IN (SELECT Data FROM dbo.fnSplitString(@Years, ','))
GROUP BY YEAR(S.ReceivalDate)

--
Chandu

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-03-01 : 08:30:30

Actually, I have 4 procedures with different conditions in where.

one where:

S.ClosedYN = 0 AND
S.Shipped = 1 AND
S.DeletedYN = 0 AND
S.OutInbound = 2 AND

second where clause:

S.ClosedYN = 1 AND
S.Shipped = 1 AND
S.FinanceDetailsYN = 0 AND
S.DeletedYN = 0 AND
S.OutInbound = 2 AND

third where clause:

S.ClosedYN = 1 AND
S.Shipped = 1 AND
S.FinanceDetailsYN = 1 AND
S.DeletedYN = 0 AND
S.OutInbound = 2 AND

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-03-01 : 08:50:28

As I have different where clauses how can I combine in a row..?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-01 : 08:54:49

see the way i gave the query like that you need to extend it to include other queries too
the rule of thumb is to put common conditions in where and add the additional ones inside case when

in your case put

S.Shipped = 1 AND
S.DeletedYN = 0 AND
S.OutInbound = 2

in where

and rest inside case...when

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-01 : 08:59:24

I think you can acheive as Webfred explained only if the number of columns are same..
Alternatively, create a table and insert data into it

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources