Thursday, November 22, 2012

Sql Server Increment Field Value in a Query

I had a need to increment a field in a database table on each execution of a stored procedure. At 1st, was planning to increment that in the C# code but once I thinked twice I knew there's an option to do that in sql query.
So, what I did is this:

DECLARE @ViewsCount int
SELECT @ViewsCount = [Views] FROM Project WHERE Oid='FAD06A96-ABE8-4A8F-81ED-07235EE813FC' AND ProjectName='My Project'
UPDATE Project
SET [Views] = (@ViewsCount + 1)
WHERE Oid='FAD06A96-ABE8-4A8F-81ED-07235EE813FC' AND ProjectName='My Project'


I am declaring int variable and then setting the value of that variable with a select query.
Once the variable has the value I am updating the field in the table.
That's all!
The field is now incremented each time that SP will be called.
Thanks for reading.

No comments:

Post a Comment