IF EXISTS (
    SELECT 1 
    FROM sys.objects 
    WHERE object_id = OBJECT_ID('[APS].[sp_UpdateRecipeLastSignature]')
)
    DROP PROCEDURE [APS].[sp_UpdateRecipeLastSignature]
GO

CREATE PROCEDURE [APS].[sp_UpdateRecipeLastSignature]
(
    @RecipeId UNIQUEIDENTIFIER
)
AS
BEGIN
    SET NOCOUNT ON;

    WITH LatestSignature AS (
        SELECT 
            sig.SaveDate,
            sig.Recipe,
            sig.RecipeSignatureID
        FROM 
            [APS].[T_RECIPE_SIGNATURE] sig
        WHERE 
            sig.Recipe = @RecipeId
            AND NOT EXISTS (
                SELECT 1 
                FROM [APS].[T_RECIPE_SIGNATURE]  sig2
                WHERE 
                    sig2.Recipe = sig.Recipe
                    AND sig2.SaveDate > sig.SaveDate
            )
    )
    UPDATE r
    SET 
        r.LastSignature = ls.RecipeSignatureID
    FROM [APS].[T_RECIPE] AS r
    JOIN LatestSignature AS ls ON r.RecipeID = ls.Recipe
END
GO
