The biggest I usuaully have is for Junction table with subtyping info: like if you  had a article with a file and you want to mark a file as the primary file for that article. (so if the file is used in more than one article for different purposes it would be repurposed by all articles)

So I would do a ArticleFile_Save proc:

Declare @ID  as Varchar(20)
SELECT @ID =exec ArticleFile_ReadID @ArticleID, @FileID)
IF(@ID is NULL)
BEGIN
SELECT @ID = exec ArticleFile_Insert @ArticleID, @FileID, @Primary, @Type
END
ELSE
BEGIN
 exec ArticleFile_Update @ArticleID, @FileID, @Primary, @Type
END

SELECT @ID



Add transaction block and the Alter proc statement  and parameter  declarations and thats as long as they get.