I have a table ([Label]) which i would like to enforce a soft limit on the number of rows a company can insert (stored procedure code below) If this stored procedure is being called frequently (e.g. a few times per second by different users), is it possible that it will insert a row over the limit due to concurrency issue? Should i use LOCK or TRANSACTION?
CREATE PROCEDURE [dbo].[admin_AddLabel] @companyId INT, @label NVARCHAR(50) AS DECLARE @limit INT = 10 DECLARE @count INT = (SELECT COUNT(1) FROM Label WHERE CompanyId = @companyId) IF @count < @limit BEGIN INSERT INTO Label (Label, CompanyId) VALUES ( @label, @companyId ) RETURN 0 END