/****** Object:  StoredProcedure [dbo].[MCC_SetPhoto4TaskID]    Script Date: 23-04-2018 10:43:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Carsten Cremer
-- Create date: 20180423
-- Description:	Add or replace the photo for a given taskID
-- =============================================
CREATE PROCEDURE  [dbo].[MCC_SetPhoto4TaskID]  @TaskIDNo int,  @Photo_ba image
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- First Delete the current photo if there is one...
	DECLARE @PicIdentity int
	SET @PicIdentity =( SELECT FKImageId FROM ImageXTab  WHERE FKFollowupNumber =@TaskIDNo );
	
	IF (@PicIdentity IS NOT NULL)
	BEGIN
  	  DELETE FROM ImageXTab WHERE FKFollowupNumber = @TaskIDNo;
	  DELETE FROM Image WHERE Id = @PicIdentity;
	END;
	 

	-- Then add the new photo

  		INSERT INTO dbo.Image WITH ( ROWLOCK ) 
		   ( Picture )
		   VALUES ( @Photo_ba )

		SELECT @@Identity
		SET @PicIdentity = @@Identity

       INSERT INTO dbo.ImageXTab WITH (ROWLOCK) (FKFollowupNumber,FKImageId) VALUES (  @TaskIDNo, @PicIdentity )



END
GO
