create proc dbo.spRaces
	@UpdateType as char(7),
	@RaceID as int = null,
	@SectionID as smallint = null,
	@SectionName as varchar(50) = null,
	@Race as varchar(50) = null,
	@RaceDescription as varchar(MAX) = null,
	@UpdateCountVar as varchar(50)
as
set nocount on
set xact_abort on
set arithabort on
declare @ErrorNum int

begin transaction

--Process a new race
if (@UpdateType = 'Insert')
begin
	insert into dbo.Races (SectionID, Race, RaceDescription) values (@SectionID, @Race, @RaceDescription)
	set @UpdateCountVar = 'Add New ' + @SectionName + ' Race';
	select @ErrorNum = @@ERROR;
end

--Process an updated race
if (@UpdateType = 'Update')
begin
	update dbo.Races
	set SectionID = coalesce(@SectionID, Position), Race = coalesce(@Race, Race), RaceDescription =  coalesce(@RaceDescription, RaceDescription)
	where Race = @RaceID;
	
	set @UpdateCountVar = 'Update ' + @SectionName + ' Race';
	select @ErrorNum = @@ERROR;
end

--Process a race removal
if (@UpdateType = 'Delete')
begin
	delete dbo.Races where ID = @RaceID;
	select @ErrorNum = @@ERROR;
end

if (@ErrorNum = 0)
begin
exec @ErrorNum = dbo.spUpdateCount @UpdateCountVar;
end

if (@ErrorNum = 0)
begin
	commit
end
else
begin
	rollback
end

return @ErrorNum;

quote:

---

Originally posted by Eagle_f90

Thanks few follow ups thought
1. Why set it to varchar? It should only ever contain Insert, Update or Delete. So why take up the extra memory of doing a varchar vs a char?
setting it to varchar is not mandatory. But current way you've char(7) you're passing options (insert,update,delete) which are only 6 characters long. so if you're sure options will be only above make it char(6).
2. @RaceID will be assigned a variable from the ASP.Net script calling it when an update or delete is ran. If an insert is ran RaceID is not needed so I am not sure why I would have to assign it a varable in the proc.
are you sure that proc will get called only from application? if somebody calls it directly from sql without passing RaceID it wont give any results
--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

---

quote:

---

Originally posted by Sachin.Nand

Cant you just use a MERGE statement and get rid of all those checks ?

PBUH

---

	insert into dbo.Races (SectionID, Race, RaceDescription) values (@SectionID, @Race, @RaceDescription)
	set @UpdateCountVar = 'Add New ' + @SectionName + ' Race';
	select @ErrorNum = @@ERROR;

	insert into dbo.Races (SectionID, Race, RaceDescription) 
        SELECT @SectionID, @Race, @RaceDescription
        WHERE NOT EXISTS (SELECT * FROM dbo.Races WHERE ... duplicate unique-key test(s) ...)
	select @ErrorNum = @@ERROR, @RowCount = @@ROWCOUNT
        IF @ErrorNum <> 0 OR @RowCount <> 1
        BEGIN
            ... handle error here ...
        END
        ELSE
        BEGIN
	    set @UpdateCountVar = 'Add New ' + @SectionName + ' Race';
        END

if (@UpdateType = 'Insert')
begin
    --Process a new race
	...
end
ELSE
if (@UpdateType = 'Update')
begin
    --Process an updated race
	...
end
ELSE
if (@UpdateType = 'Delete')
begin
    --Process a race removal
	...
end
ELSE
begin
	... process error condition - "command not recognised" ...
end

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by Sachin.Nand

Cant you just use a MERGE statement and get rid of all those checks ?

PBUH

---

I think from what I understood OP wants to control DML operations individually from application. Thats why all if conditions I guess.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by Eagle_f90

First thanks for all the imput I have made some suggested changes (like learning to count to 6 instead of 7).

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by Sachin.Nand

Cant you just use a MERGE statement and get rid of all those checks ?

PBUH

---

I think from what I understood OP wants to control DML operations individually from application. Thats why all if conditions I guess.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

I am not sure what a DML operation is or what the merge command is. The thought behind this proc is that it will be called by a single ASP.NET page that allows a loged in user to Add new race information, edit existing, or delete a mistake. Each action can only happen one at a time by each user. I decided to put everything into a single proc so keep all the data manipulation in a single area. I am open to any suggestions that will inprove performance as this site does not have many resorces to it so anything to improve performance and keep resorce usage down it a good thing.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

---

quote:

---

Originally posted by Lamprey

Having a single upsert Upsert sproc makes sense. But, I'm more curious about putting the delete in there as well.

---

quote:

---

Originally posted by Lamprey
I'm more curious about putting the delete in there as well.

---

if (@UpdateType = 'Update')
begin
	update dbo.Races
	set SectionID = coalesce(@SectionID, Position), Race = coalesce(@Race, Race), RaceDescription =  coalesce(@RaceDescription, RaceDescription)
	where Race = @RaceID;
	
	set @UpdateCountVar = 'Update ' + @SectionName + ' Race';
	select @ErrorNum = @@ERROR;
end