IDENTITY_INSERT is set to OFF - How to turn it ON?

I believe it needs to be done in a single query batch. Basically, the GO statements are breaking your commands into multiple batches and that is causing the issue. Change it to this:

SET IDENTITY_INSERT tbl_content ON
/* GO */

...insert command...

SET IDENTITY_INSERT tbl_content OFF
GO

Shouldn't you be setting identity_Insert ON, inserting the records and then turning it back off?

Like this:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET IDENTITY_INSERT tbl_content ON
GO

ALTER procedure [dbo].[spInsertDeletedIntoTBLContent]
@ContentID int, 
SET IDENTITY_INSERT tbl_content ON
...insert command...
SET IDENTITY_INSERT tbl_content OFF

Should you instead be setting the identity insert to on within the stored procedure? It looks like you're setting it to on only when changing the stored procedure, not when actually calling it. Try:

ALTER procedure [dbo].[spInsertDeletedIntoTBLContent]
@ContentID int, 

SET IDENTITY_INSERT tbl_content ON

...insert command...

SET IDENTITY_INSERT tbl_content OFF
GO