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.