Return Data and Update Row without Multiple Lookups? I have a stored procedure that looks up an article based on the article's title. But I also need to increment a column in the same table that counts the number of times the article has been viewed.

Trying to be as efficient as possible, I wanted a way to do this without performing multiple lookups. Someone pointed out the newer OUTPUT clause, which I've used below.

I'm just wondering if I'm using it in the most efficient way, if I really made it faster, and if there are any other optimizations that could be employed.

DECLARE @Slug VARCHAR(250) -- Stored procedure argument

-- declare @UpdatedArticle table variable 
DECLARE @UpdatedArticle TABLE
(
 ArtID INT,
 ArtUserID UNIQUEIDENTIFIER,
 ArtSubcategoryID INT,
 ArtTitle VARCHAR(250),
 ArtHtml VARCHAR(MAX),
 ArtDescription VARCHAR(350),
 ArtKeywords VARCHAR(250),
 ArtLicenseID VARCHAR(10),
 ArtViews BIGINT,
 ArtCreated DATETIME2(7),
 ArtUpdated DATETIME2(7)
);

UPDATE Article
 SET ArtViews = ArtViews + 1
OUTPUT
 INSERTED.ArtID,
 INSERTED.ArtUserID,
 inserted.ArtSubcategoryID,
 INSERTED.ArtTitle,
 INSERTED.ArtHtml,
 INSERTED.ArtDescription,
 INSERTED.ArtKeywords,
 INSERTED.ArtLicenseID,
 INSERTED.ArtViews,
 INSERTED.ArtUpdated,
 INSERTED.ArtCreated
INTO @UpdatedArticle
WHERE ArtSlugHash = CHECKSUM(@Slug) AND ArtSlug = @Slug AND ArtApproved = 1

SELECT a.ArtID, a.ArtUserID, a.ArtTitle, a.ArtHtml, a.ArtDescription, a.ArtKeywords, a.ArtLicenseID,
 l.licTitle, a.ArtViews, a.ArtCreated, a.ArtUpdated, s.SubID, s.SubTitle, c.CatID, c.CatTitle,
 sec.SecID, sec.SecTitle, u.UsrDisplayName AS UserName
 FROM @UpdatedArticle a
 INNER JOIN Subcategory s ON a.ArtSubcategoryID = s.SubID
 INNER JOIN Category c ON s.SubCatID = c.CatID
 INNER JOIN [Section] sec ON c.CatSectionID = sec.SecID
 INNER JOIN [User] u ON a.ArtUserID = u.UsrID
 INNER JOIN License l ON a.ArtLicenseID = l.LicID


This is used in an ASP.NET application using SQL Server 2008.
You could do it all in one step, like this.

CREATE PROCEDURE dbo.IncrementArtViews (@Slug varchar(250)) AS

UPDATE a
SET ArtViews = ArtViews + 1
OUTPUT a.ArtID, a.ArtUserID, a.ArtTitle, a.ArtHtml, a.ArtDescription,
 a.ArtKeywords, a.ArtLicenseID, l.licTitle, a.ArtViews, a.ArtCreated, 
 a.ArtUpdated, s.SubID, s.SubTitle, c.CatID, c.CatTitle, sec.SecID, 
 sec.SecTitle, u.UsrDisplayName AS UserName
FROM dbo.Article a
 INNER JOIN dbo.Subcategory s ON a.ArtSubcategoryID = s.SubID
 INNER JOIN dbo.Category c ON s.SubCatID = c.CatID
 INNER JOIN dbo.[Section] sec ON c.CatSectionID = sec.SecID
 INNER JOIN dbo.[User] u ON a.ArtUserID = u.UsrID
 INNER JOIN dbo.License l ON a.ArtLicenseID = l.LicID
WHERE a.ArtSlugHash = CHECKSUM(@Slug)
 AND a.ArtSlug = @Slug
 AND a.ArtApproved = 1