create proc spAddNewSection
	@FullName varchar(250),
	@ShortName varchar(50),
	@FolderName varchar(50),
	@PrimaryCat char(11),
	@SecondaryCat char(11) = null,
	@Synopsis varchar(MAX)
as
	--Insert new section into DB
	insert into dbo.SectionInfo (SectionTitle, FolderName, ShortName, Synopsis) values (@FullName, @FolderName, @ShortName, @Synopsis)
	--Declare and set the SectionID varibale with the autoID from the insert above
	declare @SectionID int;
	select @SectionID = SCOPE_IDENTITY();
	--Insert data for  primary cat
	insert into dbo.SectionCategories (SectionID, Category, Position) values (@SectionID, @PrimaryCat, '0');
	--Cehck if secondary cat was set and insert if needed
	if @SecondaryCat is not null
	begin
		insert into dbo.SectionCategories (SectionID, Category, Position) values (@SectionID, @SecondaryCat, '0');
	end
	--If no errors sofar run spUpdateCount, else return error code
	if @@ERROR = 0
	begin
		exec dbo.spUpdateCount 'NewSection';
		return @@ERROR;
	end
	else
	begin
		return @@ERROR;
	end

create proc spUpdateCount
@AddType varchar(50)
as
--Decare and get the count of the addtype
declare @Count int;
select @Count = UpdateCount from dbo.UpdateCounts where AddType = @AddType;
--If the count is greater then 0 incrament, else add new entry
if @Count > 0
	begin
		set @Count = @Count + 1;
		update dbo.UpdateCounts set UpdateCount = @Count where AddType = @AddType;
	end
else
	begin
		insert into dbo.UpdateCounts (AddType, UpdateCount) values (@AddType, 1);
	end

quote:

---

Originally posted by tkizer

You need to add transaction handling. I'd wrap it into TRY/CATCH too.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

CREATE PROC dbo.spAddNewSection
	@FullName varchar(250),
	@ShortName varchar(50),
	@FolderName varchar(50),
	@PrimaryCat char(11),
	@SecondaryCat char(11) = null,
	@Synopsis varchar(MAX)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
DECLARE	@intErrNo int

	BEGIN TRANSACTION
	--Insert new section into DB
	INSERT INTO dbo.SectionInfo (SectionTitle, FolderName, ShortName, Synopsis) 
		VALUES (@FullName, @FolderName, @ShortName, @Synopsis)
	SELECT	@intErrNo = @@ERROR
	--Declare and set the SectionID varibale with the autoID from the insert above
	DECLARE @SectionID int
	SELECT	@SectionID = SCOPE_IDENTITY()
	IF @intErrNo = 0
	BEGIN
		--Insert data for  primary cat
		INSERT INTO dbo.SectionCategories (SectionID, Category, Position) 
		SELECT	@SectionID, @PrimaryCat, '0'
		UNION ALL
		--Cehck if secondary cat was set and insert if needed
		SELECT	@SectionID, @SecondaryCat, '0'
		WHERE @SecondaryCat is not null
		SELECT	@intErrNo = @@ERROR
	END
	--If no errors sofar run spUpdateCount, else return error code
	IF @intErrNo = 0
	BEGIN
		EXEC @intErrNo = dbo.spUpdateCount 'NewSection'
	END
	IF @intErrNo = 0
	BEGIN
		COMMIT
	END
	ELSE
	BEGIN
		ROLLBACK
	END
	RETURN @intErrNo
GO

CREATE PROC dbo.spUpdateCount
	@AddType varchar(50)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
DECLARE	@intErrNo int,
	@intRowCount int

	BEGIN TRANSACTION

	UPDATE	dbo.UpdateCounts 
	SET	UpdateCount = UpdateCount + 1
	WHERE	AddType = @AddType
		AND UpdateCount > 0
	SELECT	@intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
	
	IF @intErrNo = 0 AND @intRowCount = 0	-- No row found with suitable UpdateCount
	BEGIN
		INSERT INTO dbo.UpdateCounts(AddType, UpdateCount) values (@AddType, 1)
		SELECT	@intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
	END

	IF @intErrNo = 0
	BEGIN
		COMMIT
	END
	ELSE
	BEGIN
		ROLLBACK
	END

	RETURN @intErrNo
GO

quote:

---

Originally posted by Eagle_f90


...
--Cehck if secondary cat was set and insert if needed
if @SecondaryCat is not null
begin
insert into dbo.SectionCategories (SectionID, Category, Position) values (@SectionID, @SecondaryCat, '0');
end
--If no errors sofar run spUpdateCount, else return error code
if @@ERROR = 0

---