sproc.sql

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		dperussina
-- Create date: 10.9.17
-- Description:	Helping tommy resolve a bug
-- =============================================
CREATE PROCEDURE tagnet_FaxProc_GetAllMessagesByPro
	-- Add the parameters for the stored procedure here
	@PartnerPrimaryOrderID varchar(30)	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	
DECLARE @orders table(Id uniqueidentifier); 
INSERT INTO @orders 
SELECT JobRecID from 
tagnet_AppointmentSPN_Orders 
WHERE PartnerPrimaryOrderID = @PartnerPrimaryorderID;

DECLARE @messages table(Id uniqueidentifier); 
INSERT INTO @messages 
SELECT DISTINCT [MessageRecID] from
[dbo].[tagnet_FaxProc_ReceivedMessage2SPNJobControlNumber]
WHERE
JobRecID in (select * from @orders);

SELECT *
  FROM [tagnet].[dbo].[tagnet_FaxProc_ReceivedMessageHeader]
  WHERE
 recID in(select * from @messages);
END
GO