SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Combine multiple procedures resultsets
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 03/01/2013 :  08:09:33  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 03/01/2013 :  08:16:01  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/01/2013 :  08:21:54  Show Profile  Reply with Quote

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


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

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2208 Posts

Posted - 03/01/2013 :  08:24:31  Show Profile  Reply with Quote
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
Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 03/01/2013 :  08:30:30  Show Profile  Reply with Quote
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
Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 03/01/2013 :  08:50:28  Show Profile  Reply with Quote
As I have different where clauses how can I combine in a row..?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/01/2013 :  08:54:49  Show Profile  Reply with Quote
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/

Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/01/2013 :  08:59:24  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000