sp to update existing or insert new row -- it this a good way to do it?
-
I have a table as shown below whose primary key is an identity. I want to have a stored procedure that will either insert or update one row and return the identity. The following stored procedure works but is it a good and effective way to solve the problem? Thanks in advance for your help, Pete
CREATE TABLE [dbo].[Alert] (
[AlertID] [int] IDENTITY ,
[AlertDate] [datetime] NOT NULL ,
[Source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IncidentNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Severity] [int] NOT NULL ,
[Processed] [int] DEFAULT 0,
[Purged] [int] DEFAULT 0,
[Category] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Text] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE dbo.xp_insAlert
@source varchar(50),
@IncidentNo varchar(50),
@Severity int,
@Category varchar(50),
@AlertText varchar(250),
@url varchar(250),
@alertNumber int OUT
AS
set @alertNumber = (select alertid from alert where source = @source and IncidentNo = @IncidentNo)
if @alertNumber is null
begin
insert into alert (alertDate,source,incidentNo,severity,category,text,url)
values(current_timestamp,@Source,@IncidentNo,@Severity,@Category,@AlertText,@url)
set @alertNumber = scope_identity()
end
else
begin
update alert set text = @alertText where alertID = @alertNumber
end
GO
-
This seems OK to me
. You could add begin/commit transaction to make sure you do not insert the same source and IncidentNo twice. You probably also want a (clustered) PRIMARY KEY on source and IncidentNo. -
1. The name prefix xp_ is typically used for extended stored procs (not written in T-SQL), I would not use it here.
2. After AS, add "set nocount on" line
3. I assume there is a unique constraint on source, IncidentNo ?
3. Is it possible to get near simultaneous inserts on same (source, IncidentNo)? If so consider adding tran around the "existence check" (set @AlertNumber = (select alertid...)) and the insert/update, and adding UPDLOCK to the "existence check."
4. Do you care if inserts or updates fail? If so, need to check @@error & then act on it
select @err = @@error
if (@err <> 0) ...
5. Here's transaction & error code structure (we avoid beginning/ending tran if we're already in one, and never know if calling proc/app has already begun a tran or not, so we check):
declare @InitialTranCount int
set @InitialTranCount = @@trancount
if @InitialTranCount = 0
begin tran
set @alertNumber = (select alertid from alert(UPDLOCK) where source = @source and IncidentNo = @IncidentNo)
...insert/update...
select @err = @@error
if (@err <> 0)
begin
...do something, maybe raiserror()
if (@@trancount > @InitialTranCount)
rollback
end
--success
if (@@trancount > @InitialTranCount)
commit
PS - Started this before I saw Bert's reply--I duplicated some of his comments, sorry.
PS - I never use the "set @var = (select ...)" syntax, seemed like extra typing to me, have used "select @var = ..." syntax. But I just learned here that the set syntax has side-benefit of initializing @var to NULL if (select ...) returns nothing. Nice to know.
-
What I feel is First rin the update statement statement as
update alert set text = @alertText where alertID = ( select alertid from alert where source = @source and IncidentNo = @IncidentNo)
and checkfor the @@rowcount if it is greater than 0 there is no need to run the insert statement obviously, if its 0 get the @alertnumber and run the insert sql.
You may want to check this with real data and running the execution plan to see which is optimal.
Thanks
Prasad Bhogadi
www.inforaise.com -
We do this type of thing quite often. However, if the record already exists, it is up to the caller to provide the ID. This avoids the select. We also always return the identity value to the caller:
CREATE PROCEDURE dbo.xp_insAlert
@source varchar(50),
@IncidentNo varchar(50),
@Severity int,
@Category varchar(50),
@AlertText varchar(250),
@url varchar(250),
@alertNumber int
AS
if @alertNumber is null
begin
insert into alert (alertDate,source,incidentNo,severity,category,text,url)
values(current_timestamp,@Source,@IncidentNo,@Severity,@Category,@AlertText,@url)
set @alertNumber = scope_identity()
-- Get the new identity value for the caller
select @alertNumber = @@identity
end
else
begin
update alert set text = @alertText where alertID = @alertNumber
end
return @alertNumber
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply