title: how to perform Two select query in one stored procedure in sql server
body: <p>I am trying to perform two select query in one stored procedure. But its not giving me any output. There is no error but records are not being displayed.</p>
  
  <p>First I tried this stored Procedure</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[Sp_PriceList_Die_Wise]
  @DieNo As Nvarchar(15),
  @MetalCode As Int
  AS
  BEGIN
  Select CP.BatchQty,CP.CastingPrice,U.UNITName As 'Unit',MachPrice,U.UNITName As 'M/C Unit' from CustomerPriceList As CP Left Outer Join UNITMaster As U On CP.MUNITID=U.UNITID where MOULDCODE=@DieNo And METALCODE=@MetalCode
  
  SELECT TOP (10) SubOADetail.OANO, SubOADetail.ID, SubOADetail.QTY, SubOADetail.RATE, UNITMaster.UNITName As Unit, OATest.MachPrices, UNITMaster_1.UNITName AS Unit FROM UNITMaster AS UNITMaster_1 RIGHT OUTER JOIN OATest ON UNITMaster_1.UNITID = OATest.MachUnitID RIGHT OUTER JOIN SubOADetail LEFT OUTER JOIN UNITMaster ON SubOADetail.UNIT = UNITMaster.UNITID ON OATest.ID = SubOADetail.ID AND OATest.OANO = SubOADetail.OANO LEFT OUTER JOIN OADetails ON SubOADetail.OANO = OADetails.OANO WHERE SubOADetail.MOULDCODE = @DieNo AND SubOADetail.METALCODE =@MetalCode  ORDER BY OADetails.OADATE DESC
  END
  </code></pre>
  
  <p>This gives me output like this<br></p>
  
  <p><img src="https://i.stack.imgur.com/Ai9MJ.png" alt="This is my output Image"> </p>
  
  <p>but I want the output in single table so I created this stored procedure</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[Sp_PriceList_Die_Wise]
  @DieNo As Nvarchar(15),
  @MetalCode As Int
  AS
  BEGIN
  WITH main AS 
  (
    Select CP.BatchQty,CP.CastingPrice,U.UNITName As 'Unit',
    MachPrice,U.UNITName As 'M/C Unit' from CustomerPriceList As CP 
    Left Outer Join UNITMaster As U On CP.MUNITID=U.UNITID 
    where MOULDCODE=@DieNo And METALCODE=@MetalCode
  ), sub AS
  (
    SELECT TOP (10) SubOADetail.OANO, SubOADetail.ID, SubOADetail.QTY, SubOADetail.RATE, 
    UNITMaster.UNITName As Unit, OATest.MachPrices, UNITMaster_1.UNITName AS MCUnit 
    FROM UNITMaster AS UNITMaster_1 RIGHT OUTER JOIN OATest ON UNITMaster_1.UNITID = OATest.MachUnitID 
    RIGHT OUTER JOIN SubOADetail LEFT OUTER JOIN UNITMaster ON SubOADetail.UNIT = UNITMaster.UNITID 
    ON OATest.ID = SubOADetail.ID AND OATest.OANO = SubOADetail.OANO LEFT OUTER JOIN OADetails 
    ON SubOADetail.OANO = OADetails.OANO WHERE SubOADetail.MOULDCODE = @DieNo 
    AND SubOADetail.METALCODE =@MetalCode  ORDER BY OADetails.OADATE DESC
  )
  SELECT * 
    FROM main m join sub s
      ON m.MachPrice = s.MachPrices
  END
  </code></pre>
  
  <p>But this gives me blank record like this </p>
  
  <p><img src="https://i.stack.imgur.com/iLqJl.png" alt="I want record in this output"></p>