CREATE TABLE dbo.Article(
	Id int IDENTITY(1,1) NOT NULL,
	Position int NULL,
	StatusId int NULL
 CONSTRAINT PK_Article PRIMARY KEY CLUSTERED 
(
	Id ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON PRIMARY
) ON PRIMARY


CREATE TABLE dbo.ArticleLanguage(
	Id int IDENTITY(1,1) NOT NULL,
	LanguageId int NULL,
	ArticleId int NULL,
	Name varchar(100) NULL
 CONSTRAINT PK_ArticleLanguage PRIMARY KEY CLUSTERED 
(
	Id ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON PRIMARY
) ON PRIMARY
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE dbo.ArticleLanguage  WITH CHECK ADD  CONSTRAINT FK_ArticleLanguage_Article FOREIGN KEY(ArticleId)
REFERENCES dbo.Article (Id)
GO
ALTER TABLE dbo.ArticleLanguage CHECK CONSTRAINT FK_ArticleLanguage_Article


CREATE TABLE dbo.ArticleListing(
	Id int IDENTITY(1,1) NOT NULL,
	TransactionGuid varchar(40) NULL,
	TransactionDate datetime NULL CONSTRAINT DF_ArticleListing_TransactionDate  DEFAULT (getdate()),
	LanguageId int NULL,
	ArticleId int NULL,
	ArticleLanguageId int NULL,
	Name varchar(100) NULL,
	Position int NULL,
	StatusId int NULL
 CONSTRAINT PK_ArticleListing PRIMARY KEY CLUSTERED 
(
	Id ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON PRIMARY
) ON PRIMARY


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: Returns Article and ArticleLanguage listing in one table
-- =============================================
CREATE PROCEDURE dbo._ArticleListing_GetListing
(
	@LanguageId int,
	@ArticleId int = null,	
	@StatusId int = null
)
AS
BEGIN

	--Delete old records
	DELETE FROM ArticleListing WHERE TransactionDate < DATEADD(minute, -2, GETDATE())

	--Create new GUID for this transaction
	DECLARE @TransactionGuid varchar(40)
	SET @TransactionGuid = NEWID()

	--Insert from Article
	INSERT INTO ArticleListing
	(
			TransactionGuid,
			ArticleId,
			Position,
			StatusId
	)
	SELECT
			@TransactionGuid,
			Id,
			Position,
			StatusId
	FROM	Article
	WHERE (@ArticleId IS NULL OR Id = @ArticleId) AND
				(@StatusId IS NULL OR StatusId = @StatusId)

	--Update from ArticleLanguage
	UPDATE  ArticleListing SET
					ArticleListing.LanguageId = ArticleLanguage.LanguageId,
					ArticleListing.ArticleLanguageId = ArticleLanguage.Id,
					ArticleListing.Name = ArticleLanguage.Name
	FROM		ArticleLanguage LEFT JOIN
					ArticleListing ON ArticleListing.ArticleId = ArticleLanguage.ArticleId
	WHERE		TransactionGuid = @TransactionGuid AND
					ArticleListing.ArticleId = ArticleLanguage.ArticleId AND
					ArticleLanguage.LanguageId = @LanguageId AND
					(@ArticleLanguageId IS NULL OR ArticleLanguage.Id = @ArticleLanguageId)

	--Delete not valid records
DELETE FROM ArticleListing WHERE TransactionGuid = @TransactionGuid AND 
(LanguageId IS NULL OR ArticleLanguageId IS NULL)

	SELECT	Id,
					TransactionGuid,
					TransactionDate,
					LanguageId,
					ArticleId,
					ArticleLanguageId,
					Name,
					Position,
					StatusId
	FROM		ArticleListing
	WHERE		TransactionGuid = @TransactionGuid
	ORDER BY Position
END

Message: Transaction (Process ID 166) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior
runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at
Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteReader(DbCommand command, CommandBehavior cmdBehavior) at
Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) at MyProject.Data.Utility.ExecuteReader(Database database, DbCommand dbCommand) at
MyProject.Data.SqlClient.SqlArticleListingProviderBase.GetListing(TransactionManager transactionManager, Int32 start, Int32 pageLength, Nullable`1 languageId, Nullable`1 articleId, Nullable`1 statusId) at
MyProject.Data.Bases.ArticleListingProviderBaseCore.GetListing(Nullable`1 languageId, Nullable`1 articleId, Nullable`1 statusId) at
UserControls_ArticleGrid.Page_Load(Object sender, EventArgs e) at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) at
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at
System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

CREATE PROCEDURE dbo.usp_ArticleListing_GetListing
(
	@LanguageID INT,
	@ArticleID INT = NULL,
	@StatusID INT = NULL
)
AS

SET NOCOUNT ON

--Delete old records
DELETE
FROM	ArticleListing
WHERE	TransactionDate < DATEADD(MINUTE, -2, CURRENT_TIMESTAMP)

-- Create new GUID for this transaction
DECLARE	@TransactionGuid UNIQUEIDENTIFIER

SET	@TransactionGuid = NEWID()

--Insert from Article
INSERT		ArticleListing
		(
			TransactionGuid,
			ArticleId,
			Position,
			StatusId,
			LanguageId,
			ArticleLanguageId,
			[Name]
		)
OUTPUT		INSERTED.Id,
		INSERTED.TransactionGuid,
		INSERTED.TransactionDate,
		INSERTED.LanguageId,
		INSERTED.ArticleId,
		INSERTED.ArticleLanguageId,
		INSERTED.[Name],
		INSERTED.Position,
		INSERTED.StatusId
SELECT		@TransactionGuid,
		a.Id,
		a.Position,
		a.StatusId
		al.LanguageId,
		al.Id,
		al.Name
FROM		Article AS a
INNER JOIN	ArticleLanguage AS al ON al.ArticleId = a.Id
WHERE		(a.Id = @ArticleId OR @ArticleId IS NULL)
		AND (a.StatusId = @StatusId OR @StatusId IS NULL)
		AND al.LanguageId = @LanguageId
		AND (al.Id = @ArticleLanguageId OR @ArticleLanguageId IS NULL)
ORDER BY	a.Position