可以在SQL Server 2000中的存储过程中锁定行吗？

M Wells< pl ********** @ planetthoughtful.org>在消息新闻中写道：< j1 ******************************** @ 4ax.com>。 ..

M Wells <pl**********@planetthoughtful.org> wrote in message news:<j1********************************@4ax.com>. ..

大家好，

我有一个表，其中包含预生成的成员ID。

此表用于指定可用的成员ID网站
选择在网站注册的访问者

因此，从概念上讲，这个过程一直是从网站（在ASP中）到：

- 选择成员表中的最高记录，其中分配的标志
= 0
- 更新行中包含有关新成员的详细信息并将
分配的标志更改为1

- 将选定的会员ID返回到网页

现在我正在处理可能会有短暂，高流量的注册期限的想法，所以我'我试图构建一个方法（存储过程？），如果在
上发生多于1个注册请求，将确保
select语句不返回相同的成员ID。同一时刻。

所以，我的问题是，有没有办法，一旦选择了一个记录，
排除来自其他选择请求的记录，在存储过程的范围内？

即：

- 执行select语句并立即锁定行;在那个确切的时刻运行的任何其他
选择语句将返回一个不同的
行返回，并且类似地将其锁定，并且发生同样多的
同时发生的选择语句
- 行更新详细信息并更新标志以指示
成员ID不再被取消分配
- 行被释放用于一般用途等

如果我在上面建议的是实用，任何人都可以帮助我
确定一种不同的方法来实现相同的结果吗？

任何帮助都非常，非常感谢！

非常温暖，

Murray

Hi All,

I have a table that holds pregenerated member IDs.

This table is used to assign an available member id to web site
visitors who choose to register with the site

So, conceptually the process has been, from the site (in ASP), to:

- select the top record from the members table where the assigned flag
= 0

- update the row with details about the new member and change the
assigned flag to 1

- return the selected member id to the web page

Now I''m dealing with the idea that there may be brief, high traffic
periods of registration, so I''m trying to build a method (stored
procedure?) that will ensure the same member id isn''t returned by the
select statement if more than 1 request to register happens at the
same instant.

So, my question is, is there a way, once a record has been selected,
to exclude that record from other select requests, within the bounds
of a stored procedure?

ie:

- select statement is executed and row is instantly locked; any other
select statement running at that exact moment will receive a different
row returned and sill similarly lock it, ad nauseum for as many
simultaneous select statements as take place
- row is updated with details and flag is updated to indicate the
member id is no longer unassigned
- row is released for general purposes etc

If what I''m suggesting above isn''t practical, can anyone help me
identify a different way of achieving the same result?

Any help immensely, immensely appreciated!

Much warmth,

Murray

从您的描述中不清楚为什么需要提前生成ID

- 更简单的方法可能是使用IDENTITY列，

并直接插入到成员表中以进行新注册。然后，
可以使用

scope_identity（）将系统生成的值返回给客户端。当然，这假定会员ID是

只是一个数字，没有其他含义：


创建表dbo.Members（

MemberID int identity（1,1）主键，

FirstName varchar（50）not null，

LastName varchar（50）not null

）

go


插入dbo.Members（FirstName，LastName）

values（'''Murray' '，''Wells''）


选择''你的ID是：''+ cast（scope_identity（）as char（2））

go


如果您确实需要对会员ID的价值进行一些控制，那么

其中一种方法可能是合适的：
> http://groups.google.com/groups?q=sq...ewin.ch&rnum = 1


如果这没有帮助，你可以哇nt发布你的成员表的结构（CREATE

TABLE）以及一些示例数据，并解释你想要返回给客户端的
。 />

Simon

It''s not clear from your description why you need to generate the IDs
in advance - a simpler approach might be to use an IDENTITY column,
and insert directly into the members table for a new registration. You
can then return the system-generated value to the client using
scope_identity(). This assumes, of course, that the membership ID is
simply a number, with no other meaning:

create table dbo.Members (
MemberID int identity(1,1) primary key,
FirstName varchar(50) not null,
LastName varchar(50) not null
)
go

insert into dbo.Members (FirstName, LastName)
values (''Murray'', ''Wells'')

select ''Your ID is: '' + cast(scope_identity() as char(2))
go

If you do need some control over the value of the membership ID, then
one of these approaches might be suitable:

http://groups.google.com/groups?q=sq...ewin.ch&rnum=1

If this doesn''t help, you may want to post the structure (CREATE
TABLE) of your members table, along with some sample data, and explain
exactly what you want to return to the client.

Simon

2004年2月4日23:44:05 -0800， sq*@hayes.ch （Simon Hayes）写道：

On 4 Feb 2004 23:44:05 -0800, sq*@hayes.ch (Simon Hayes) wrote:

M Wells< pl ********** @ planetthoughtful.org&gt ;在消息新闻中写道：< j1 ******************************** @ 4ax.com>。 ..

M Wells <pl**********@planetthoughtful.org> wrote in message news:<j1********************************@4ax.com>. ..

大家好，

[这里有snippage]

从你的描述中不清楚为什么你需要生成ID
提前 - 一种更简单的方法可能是使用IDENTITY列，
并直接插入到成员表中以进行新注册。然后，您可以使用
scope_identity（）将系统生成的值返回给客户端。当然，这假设会员ID只是一个数字，没有其他含义：

创建表dbo.Members（
MemberID int identity（1,1）主键，
FirstName varchar（50）not null，
LastName varchar（50）not null



插入dbo.Members（ FirstName，LastName）
values（''Murray''，''Wells''）

选择''你的ID是：''+ cast（scope_identity（）as char（2） ）
go

如果您确实需要控制会员ID的价值，那么这些方法中的一种可能是合适的：

http://groups.google.com/groups?q=sq...ewin.ch&rnum=1

如果这没有帮助，您可能想要发布您的成员表的结构（CREATE
TABLE）以及一些示例数据，并解释你想要返回给客户的确切内容。

[ here there be snippage ]

It''s not clear from your description why you need to generate the IDs
in advance - a simpler approach might be to use an IDENTITY column,
and insert directly into the members table for a new registration. You
can then return the system-generated value to the client using
scope_identity(). This assumes, of course, that the membership ID is
simply a number, with no other meaning:

create table dbo.Members (
MemberID int identity(1,1) primary key,
FirstName varchar(50) not null,
LastName varchar(50) not null
)
go

insert into dbo.Members (FirstName, LastName)
values (''Murray'', ''Wells'')

select ''Your ID is: '' + cast(scope_identity() as char(2))
go

If you do need some control over the value of the membership ID, then
one of these approaches might be suitable:

http://groups.google.com/groups?q=sq...ewin.ch&rnum=1

If this doesn''t help, you may want to post the structure (CREATE
TABLE) of your members table, along with some sample data, and explain
exactly what you want to return to the client.

嗨西蒙，


非常感谢你们你的帮助！


不幸的是，需要使用预先生成的成员ID已经放弃了b $ b问题;这不是我可以控制的东西。


我查看了你提供的链接中的两个例子。第一个对我来说是有意义的，但我不能制定一个查询，让它完全按照我想要的那样完成。而且我还不太了解锁定SQL Server

以了解锁定提示（UPDLOCK）是否符合我的预期。


为了给你一些额外的细节，我的测试表定义如下：


if exists（select * from dbo.sysobjects where id =

object_id（N ''[dbo]。[tblmembers]''）和OBJECTPROPERTY（id，

N''IsUserTable''）= 1）

drop table [dbo]。[ tblmembers]

GO


CREATE TABLE [dbo]。[tblmembers]（

[recid] [int] IDENTITY（1 ，1）NOT NULL，

[memid] [数字]（18,0）NULL，

[memname] [varchar]（50）COLLATE SQL_Latin1_General_CP1_CI_AS

NULL，

[mememail] [varchar]（255）COLLATE

SQL_Latin1_General_CP1_CI_AS NULL，

[已激活] [bit] NULL

）ON [PRIMARY]

GO


ALTER TABLE [dbo]。[tblmembers] WITH NOCHECK ADD

CONSTRAINT [PK_tblmembers] PRIMARY KEY CLUSTERED >
（

[recid]

）ON [PRIMARY]

GO


创建独特的索引[IX_tblmembers_memid] ON

[dbo]。[tblmembers]（[memid]）ON [PRIMARY]

GO

INSERT tblmembers（recid，memid，memname，mememail，激活）

VALUES（''1''，'''1000001''，''John Smith''，''j *** @ smith。 com''，''1''）

INSERT tblmembers（recid，memid，memname，mememail，激活）

VALUES（'''''，''1000002 ''，''Jen Smith''，'s **** @ jen.com''，''1''）

INSERT tblmembers（recid，memid，memname，mememail，activated ）

VALUES（'''''''''1000003''，''''，''''，''0''）

INSERT tblmembers（ recid，memid，memname，mememail，激活）

VALUES（'''''，'''1000004''，''''，''''，''''''br />
INSERT tblmembers（recid，memid，memname，mememail，激活）

VALUES（'''''，'' 1000005''，''''，''''，''0''）


所以，基本概念是当会员注册表格是
$时b $ b提交，我想去获取下一个未分配的成员ID

（激活= 0）将激活的标志更改为1并更新memname和

mememail字段从表单传递的参数。然后我需要

将该记录的memid字段中的值返回到确认

页面。


I可以做到这一切，但我还没有能够满足自己

如果我同时注册两个
请求就不会有问题。


从本质上讲，考虑到上面的示例数据，我正试图找到一种方法，如果有两个同时注册的请求，那么他们

返回成员编号为1000003的行，其中一个是

返回成员编号为1000004的行。并且，它可能是

不用说，如果有3个同步请求，第三个

将返回成员ID为1000005的行，所以

on 。换句话说，没有两个注册应该返回相同的成员

id。


鉴于您在链接中提供的示例，我试图这样做

类似于：


声明@rowid数字


UPDATE tblmembers set @ rowid = selmem.recid，激活= 1其中recid =

（选择TOP 1从tblmembers recid =激活= 0）作为selmem


返回@rowid


但是，当然，这是无效的语法，因为看起来你不能
在UPDATE语句中为子查询提供别名，因为我有

尝试在上面做。


但是，概念是在相同的

语句中执行select和update，返回所选/更新的recid行，然后

使用该recid值执行另一个更新查询等，以提供

成员名称和电子邮件详细信息。


如果这只会让人感到困惑，我很抱歉希望是它

更好地解释了我希望实现的目标...


再次感谢您的帮助！


温暖，

Murray

Hi Simon,

Thank you very much for your help with this!

Unfortunately, the need to use pre-generated member ids is a foregone
issue; it''s not something I have any control over.

I looked at the two examples in the link you provided. The first makes
sense to me, but I can''t formulate a query that makes it do exactly
what I want. And I don''t yet know enough about locking in SQL Server
to know if the locking hint (UPDLOCK) does what I hope it will do.

To give you a little extra detail, my test table definition is:

if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[tblmembers]'') and OBJECTPROPERTY(id,
N''IsUserTable'') = 1)
drop table [dbo].[tblmembers]
GO

CREATE TABLE [dbo].[tblmembers] (
[recid] [int] IDENTITY (1, 1) NOT NULL ,
[memid] [numeric](18, 0) NULL ,
[memname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[mememail] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[activated] [bit] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblmembers] WITH NOCHECK ADD
CONSTRAINT [PK_tblmembers] PRIMARY KEY CLUSTERED
(
[recid]
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [IX_tblmembers_memid] ON
[dbo].[tblmembers]([memid]) ON [PRIMARY]
GO
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES(''1'',''1000001'',''John Smith'',''j***@smith.com'',''1'')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES(''2'',''1000002'',''Jen Smith'',''s****@jen.com'',''1'')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES(''3'',''1000003'','''','''',''0'')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES(''4'',''1000004'','''','''',''0'')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES(''5'',''1000005'','''','''',''0'')

So, the basic concept is that when a membership registration form is
submitted, I want to go and get the next unassigned member id
(activated=0) change the activated flag to 1 and update memname and
mememail fields with parameters passed from the form. I then need to
return the value in that record''s memid field back to the confirmation
page.

I can do all of this, but I haven''t been able to satisfy myself that
there won''t be a problem if I get two simultaneous registration
requests.

In essence, I''m trying to figure a way, given the above sample data,
that if there are two simultaneous registration requests, one of them
is returned the row with the member id of 1000003 and one of them is
returned the row with the member id of 1000004. And, it probably goes
without saying, that if there are 3 simultaneous requests, the third
one would be returned the row with the member id of 1000005, and so
on. In other words, no two registrations should return the same member
id.

Given the examples you provided in the link, I was trying to do do
something similar to:

declare @rowid numeric

UPDATE tblmembers set @rowid=selmem.recid, activated = 1 where recid =
(SELECT TOP 1 recid from tblmembers where activated = 0) as selmem

return @rowid

But, of course, this is invalid syntax, since it seems you can''t
provide an alias to a subquery in an UPDATE statement as I have
attempoted to do above.

However, the concept was to perform the select and update in the same
statement, return the recid of the selected / updated row, and then
use that recid value to perform another update query etc to provide
the member name and email details.

I''m sorry if this only confuses matters, but my hope is that it
explains a little better what I''m hoping to achieve...

Thank you, again, for your help!

Much warmth,

Murray

" ; M Wells &LT; PL ********** @ planetthoughtful.org&GT;在消息中写道

news：un ******************************** @ 4ax.com ...

"M Wells" <pl**********@planetthoughtful.org> wrote in message
news:un********************************@4ax.com...

2004年2月4日23:44:05 -0800， sq*@hayes.ch （西蒙Hayes）写道：

On 4 Feb 2004 23:44:05 -0800, sq*@hayes.ch (Simon Hayes) wrote:

M Wells< pl ********** @ planetthoughtful.org>写在消息
news：< j1 ******************************** @ 4ax.com>。 ..

M Wells <pl**********@planetthoughtful.org> wrote in message news:<j1********************************@4ax.com>. ..

大家好，
[这里有瑕疵]

Hi All,
[ here there be snippage ]

从你的描述中不清楚为什么你需要提前生成ID - 更简单的方法可能是使用IDENTITY列，
并直接插入到成员表中以进行新注册。然后，您可以使用
scope_identity（）将系统生成的值返回给客户端。当然，这假设会员ID只是一个数字，没有其他含义：

创建表dbo.Members（
MemberID int identity（1,1）主键，
FirstName varchar（50）not null，
LastName varchar（50）not null



插入dbo.Members（ FirstName，LastName）
values（''Murray''，''Wells''）

选择''你的ID是：''+ cast（scope_identity（）as char（2） ）
go

如果你确实需要对会员ID的价值进行一些控制，那么
其中一种方法可能是合适的：

It''s not clear from your description why you need to generate the IDs
in advance - a simpler approach might be to use an IDENTITY column,
and insert directly into the members table for a new registration. You
can then return the system-generated value to the client using
scope_identity(). This assumes, of course, that the membership ID is
simply a number, with no other meaning:

create table dbo.Members (
MemberID int identity(1,1) primary key,
FirstName varchar(50) not null,
LastName varchar(50) not null
)
go

insert into dbo.Members (FirstName, LastName)
values (''Murray'', ''Wells'')

select ''Your ID is: '' + cast(scope_identity() as char(2))
go

If you do need some control over the value of the membership ID, then
one of these approaches might be suitable:

http://groups.google.com/groups?q=sq...=en&lr=&ie=UTF

- 8& oe = UTF-8& selm = 40044e65％241_2％40news.bluewin.ch& rnum = 1
如果这没有帮助，你可能想要发布结构（CREATE
TABLE）你的我mbers表，以及一些样本数据，并准确地解释你想要返回给客户的内容。

-8&oe=UTF-8&selm=40044e65%241_2%40news.bluewin.ch&rnum=1
If this doesn''t help, you may want to post the structure (CREATE
TABLE) of your members table, along with some sample data, and explain
exactly what you want to return to the client.

嗨西蒙，

谢谢非常感谢你的帮助！

不幸的是，需要使用预先生成的成员id是一个已经放弃的问题;这不是我可以控制的东西。

我查看了你提供的链接中的两个例子。第一个对我有意义，但我不能制定一个查询，使它完全符合我想要的。我还不太了解锁定SQL Server
以了解锁定提示（UPDLOCK）是否符合我希望它会做的事情。

为您提供一些额外的细节，我的测试表定义是：

如果存在（从dbo.sysobjects中选择*，其中id =
object_id（N''[dbo]。[tblmembers]''）和OBJECTPROPERTY（id ，
N''IsUserTable''）= 1）
drop table [dbo]。[tblmembers]
GO

CREATE TABLE [dbo]。[tblmembers] （
[recid] [int] IDENTITY（1,1）NOT NULL，
[memid] [数字]（18,0）NULL，
[memname] [varchar]（50） COLLATE SQL_Latin1_General_CP1_CI_AS
NULL，
[mememail] [varchar]（255）COLLATE
SQL_Latin1_General_CP1_CI_AS NULL，
[已激活] [bit] NULL
）ON [PRIMARY]
GO

ALTER TABLE [dbo]。[tblmembers] WITH NOCHECK ADD
CONSTRAINT [PK_tblmembers] PRIMARY KEY CLUSTERED
（
[recid] >）[主要]


创建独特的索引[IX_tblmembers_记住]
[dbo]。[tblmembers]（[memid]）ON [PRIMARY]
GO

INSERT tblmembers（recid，memid，memname，mememail，activated） INSERT tblmembers（ recid，memid，memname，mememail，激活）
VALUES（'''''''''1000002''，''Jen Smith''，'s s@@@@@@@@@ '1''）
INSERT tblmembers（recid，memid，memname，mememail，激活）
VALUES（'''''，'''''''''''''''''''''''''''''''''' ''0'）
INSERT tblmembers（recid，memid，memname，mememail，激活）
VALUES（'''''''''''''''''''''''''''''''''''''''''' ，''0'）
INSERT tblmembers（recid，memid，memname，mememail，激活）
VALUES（'''''，''''''''''''''''''''''''''''' '，''0''）

所以，基本的概念是，当提交会员登记表时，我会nt去获取下一个未分配的成员id
（激活= 0）将激活的标志更改为1并使用从表单传递的参数更新memname和
mememail字段。然后我需要将该记录的memid字段中的值返回到确认页面。

我可以完成所有这些，但我还没有能够让自己满意的是，如果我同时注册两个请求就不会有问题。

本质上，我正在努力想办法，鉴于以上样本数据，
如果有两个同时注册请求，其中一个
返回成员ID为1000003的行，其中一个是
返回行会员ID为1000004.而且，它可能会不用说，如果有3个同步请求，第三个
会返回成员ID为1000005的行，所以
上。换句话说，没有两个注册应返回相同的成员
ID。

鉴于您在链接中提供的示例，我试图做的事情类似于：

声明@rowid数字

更新tblmembers设置@ rowid = selmem.recid，激活= 1其中recid =
（SELECT TOP 1从tblmembers recid中激活= 0 ）作为selmem

返回@rowid

但是，当然，这是无效的语法，因为它似乎不能为子查询提供别名在UPDATE语句中我已经尝试过上面做的。

然而，这个概念是在同一个
语句中执行select和update，返回选中的recid /更新行，然后
使用该recid值执行另一个更新查询等，以提供会员姓名和电子邮件详细信息。

对不起，如果这只会让人感到困惑很重要，但我希望它能更好地解释我希望实现的目标......

再次感谢你的帮助！

温暖，穆雷

Hi Simon,

Thank you very much for your help with this!

Unfortunately, the need to use pre-generated member ids is a foregone
issue; it''s not something I have any control over.

I looked at the two examples in the link you provided. The first makes
sense to me, but I can''t formulate a query that makes it do exactly
what I want. And I don''t yet know enough about locking in SQL Server
to know if the locking hint (UPDLOCK) does what I hope it will do.

To give you a little extra detail, my test table definition is:

if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[tblmembers]'') and OBJECTPROPERTY(id,
N''IsUserTable'') = 1)
drop table [dbo].[tblmembers]
GO

CREATE TABLE [dbo].[tblmembers] (
[recid] [int] IDENTITY (1, 1) NOT NULL ,
[memid] [numeric](18, 0) NULL ,
[memname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[mememail] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[activated] [bit] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblmembers] WITH NOCHECK ADD
CONSTRAINT [PK_tblmembers] PRIMARY KEY CLUSTERED
(
[recid]
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [IX_tblmembers_memid] ON
[dbo].[tblmembers]([memid]) ON [PRIMARY]
GO
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES(''1'',''1000001'',''John Smith'',''j***@smith.com'',''1'')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES(''2'',''1000002'',''Jen Smith'',''s****@jen.com'',''1'')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES(''3'',''1000003'','''','''',''0'')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES(''4'',''1000004'','''','''',''0'')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES(''5'',''1000005'','''','''',''0'')

So, the basic concept is that when a membership registration form is
submitted, I want to go and get the next unassigned member id
(activated=0) change the activated flag to 1 and update memname and
mememail fields with parameters passed from the form. I then need to
return the value in that record''s memid field back to the confirmation
page.

I can do all of this, but I haven''t been able to satisfy myself that
there won''t be a problem if I get two simultaneous registration
requests.

In essence, I''m trying to figure a way, given the above sample data,
that if there are two simultaneous registration requests, one of them
is returned the row with the member id of 1000003 and one of them is
returned the row with the member id of 1000004. And, it probably goes
without saying, that if there are 3 simultaneous requests, the third
one would be returned the row with the member id of 1000005, and so
on. In other words, no two registrations should return the same member
id.

Given the examples you provided in the link, I was trying to do do
something similar to:

declare @rowid numeric

UPDATE tblmembers set @rowid=selmem.recid, activated = 1 where recid =
(SELECT TOP 1 recid from tblmembers where activated = 0) as selmem

return @rowid

But, of course, this is invalid syntax, since it seems you can''t
provide an alias to a subquery in an UPDATE statement as I have
attempoted to do above.

However, the concept was to perform the select and update in the same
statement, return the recid of the selected / updated row, and then
use that recid value to perform another update query etc to provide
the member name and email details.

I''m sorry if this only confuses matters, but my hope is that it
explains a little better what I''m hoping to achieve...

Thank you, again, for your help!

Much warmth,

Murray

我认为这就是你要找的东西，假设你的定义是'b $ b''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' />
激活值为0的行：

声明@memid int，

@memname varchar（50），>
@mememail varchar（255）

set @memname =''John Doe''

set @mememail =''j ** *@doe.com''


开始转发


选择@memid = min（memid）

来自tblmembers with（updlock）

where activated = 0

update tblmembers

set activated = 1，memname = @memname，mememail = @mememail

其中memid = @memid


提交


选择@memid

因为你要返回memid，但是更新其他列，你不能
使用我在链接中建议的UPDATE语法 - 你必须使用

锁定提示，它必须在一个事务中。请注意，我没有在上面的代码中添加任何错误处理，但你绝对应该在你的真实代码中加上

。这是一个有用的资源：

http： //www.sommarskog.se/error-handling-II.html


另外一点是同时使用recid和memid列似乎是

冗余 - 表的自然主键似乎是memid（并且
mememail也可能是候选键），所以目前还不清楚recid的目的是什么？
服务，虽然我感谢您可能无法完全控制

架构，并且您可能在这里简化了您的实际数据。


Simon

I think this is what you''re looking for, assuming that your definition of
the ''next'' row is the one with the lowest value for memid within the set of
rows which have an activated value of 0:

declare @memid int,
@memname varchar(50),
@mememail varchar(255)

set @memname = ''John Doe''
set @mememail = ''j***@doe.com''

begin tran

select @memid = min(memid)
from tblmembers with(updlock)
where activated = 0

update tblmembers
set activated = 1, memname = @memname, mememail = @mememail
where memid = @memid

commit

select @memid
Since you''re returning the memid, but updating the other columns, you can''t
use the UPDATE syntax I suggested in the link - you''ll have to use the
locking hint, which needs to be inside a transaction. Note that I haven''t
put any error handling in the code above, but you should definitely put it
in your real code. Here is a helpful resource:

http://www.sommarskog.se/error-handling-II.html

One other point is that having both the recid and memid columns seems to be
redundant - the natural primary key of the table appears to be memid (and
mememail may also be a candidate key), so it''s not clear what purpose recid
serves, although I appreciate that you may not have complete control over
the schema, and that you may have simplified your real data here.

Simon