SPROC.SQL

/****** Object:  StoredProcedure [dbo].[GetPrintJobs]    Script Date: 31-07-2019 20:34:44 ******/
DROP PROCEDURE [dbo].[GetPrintJobs]
GO

/****** Object:  StoredProcedure [dbo].[GetPrintJobs]    Script Date: 31-07-2019 20:34:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Shishir Thakur
-- Create date: 07/17/2019
-- Description:	Gets the print jobs for a given facility
-- =============================================
CREATE PROCEDURE [dbo].[GetPrintJobs] 
	@FacilityId varchar(10),
	@GetAll bit = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @WaybillId varchar(30);

	SELECT TOP(1) @WaybillId = A.WAYBILL_ID 
	FROM (SELECT DISTINCT(WAYBILL_ID)
			FROM PrintJobs 
			WHERE [FACILITY_ID] = @FacilityId
			AND [STATUS] = 1 
			AND	[FULL_GENERATED] = 1
		) AS A

    -- Insert statements for procedure here
	IF @GetAll = 1
	BEGIN
		SELECT [PRINTJOB_ID]
			  ,[FACILITY_ID]
			  ,[PRINTER_ID]
			  ,[WAYBILL_ID]
			  ,[ADDRESS1]
			  ,[FINAL_FACILITY]
			  ,[ITEM_ID]
			  ,[STATUS]
			  ,[CREATE_DATE]
			  ,[SENT_DATE]
			  ,[PRINT_DATE]
			  ,[SHIP_REF]
			  ,[CONTENTS]
			  ,[FULL_GENERATED]
			  ,[CreatedDate]
			  ,[CreatedBy]
			  ,[ModifiedDate]
			  ,[ModifiedBy]
		FROM [dbo].[PrintJobs]
		WHERE [FACILITY_ID] = @FacilityId
		AND [STATUS] = 1
		AND [FULL_GENERATED] = 1
	END
	ELSE
	BEGIN
		SELECT	[PRINTJOB_ID]
				,[FACILITY_ID]
				,[PRINTER_ID]
				,[WAYBILL_ID]
				,[ADDRESS1]
				,[FINAL_FACILITY]
				,[ITEM_ID]
				,[STATUS]
				,[CREATE_DATE]
				,[SENT_DATE]
				,[PRINT_DATE]
				,[SHIP_REF]
				,[CONTENTS]
				,[FULL_GENERATED]
				,[CreatedDate]
				,[CreatedBy]
				,[ModifiedDate]
				,[ModifiedBy]
			FROM [dbo].[PrintJobs]
		WHERE [FACILITY_ID] = @FacilityId
		AND WAYBILL_ID = @WaybillId
		AND [STATUS] = 1
		AND [FULL_GENERATED] = 1
	END
END
GO