SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.Category_Add
	@CategoryName nvarchar(255)
	,@CategoryStatus bit
	,@CategoryID int OUTPUT
AS

SET NOCOUNT ON

INSERT INTO dbo.Category(CategoryName, CategoryOrder, CategoryStatus)
SELECT @CategoryName
	,COALESCE(MAX(CategoryOrder), 0) + 1
	,@CategoryStatus
FROM dbo.Category WITH (UPDLOCK, HOLDLOCK)

SET @CategoryID = SCOPE_IDENTITY()
GO

-- to use
DECLARE @CategoryID int
EXEC dbo.Category_Add 'NewName', 1, @CategoryID OUTPUT
SELECT @CategoryID

quote:

---

INSERT INTO dbo.Category(CategoryName, CategoryOrder, CategoryStatus)
SELECT @CategoryName
,COALESCE(MAX(CategoryOrder), 0) + 1
,@CategoryStatus
FROM dbo.Category WITH (UPDLOCK, HOLDLOCK)

---