April 15, 2009 at 11:30 am
I'm writing a stored procedure that will execute 2 statements on the table. The table structure will look like this:
CREATE TABLE [dbo].[table1](
[id1] [int] NOT NULL,
[id2] [int] NOT NULL,
[text] [varchar](4000) NULL,
CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
(
[id1] ASC,
[id2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Here are some sample values:
id1 id2 text
1 1 hello
1 2 world
2 1 how
2 2 are
2 3 you
The first statement of the proc will retrieve max(id2) for a specific id1 value. Then the proc needs to do an INSERT statement on the same table. I could easily do this, but I need to be sure that the table gets locked at the beginning of the first statement and stays locked until the second statement is finished.
I need to ensure that this happens - or something logically similar so that two separate users can't create the same id2 value and cause an error due to the PK.
Any help on this is greatly appreciated. Thanks!
April 15, 2009 at 12:31 pm
Something along these lines?
create procedure dostuff (@id1 int)
as
insert into table1 (id1, id2, text)
select @id1, max(id2) + 1, 'new' from table1 where id1 = @id1;
go
April 15, 2009 at 12:36 pm
Ah yes, thank you Matt. I had a feeling i was over-complicating things. This should do the trick. Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply