auto-increment nvarchar column

Hi experts!

The situation:I have a table containing the MemberID and related member's info. The MemberID is the pk and should be incremented for every new member. However, the ID is in "nvarchar" type, since it consists of an "IM" prefix. I used the following stored procedure to insert new member to the table.

CREATE PROCEDURE dbo.spInsertNewMember
@.parMemberTitle nvarchar(2),
@.parMemberFirstName nvarchar(40),
@.parMemberLastName nvarchar(40)
AS
DECLARE @.LastMemberID AS nvarchar(16)
DECLARE @.NextMemberID AS nvarchar(16)
SET @.LastMemberID = (SELECT ISNULL(MAX(MemberID),'IM000000') FROM MemberInfo (UPDLOCK))
SET @.xx = SET @.NextMemberID = 'IM' + RIGHT(1000000 + (CAST(RIGHT(@.LastMemberID,6) AS INT) + 1), 6)
INSERT INTO MemberInfo (MemberID, MemberTitle, MemberFirstName, MemberLastName) VALUES (@.NextMemberID, @.parMemberTitle, @.parMemberFirstName, @.parMemberLastName)
RETURN

I want to ask if this sp can handle any concurrent insert to the MemberID table.

Thanks.

Hi,

why arent you doing the evaluation in the INSERT statement rather than separate ? This would minimize the concurrency effect.

(SELECT ISNULL(MAX(MemberID),'IM000000') FROM MemberInfo (UPDLOCK))
SET @.xx = SET @.NextMemberID = 'IM' + RIGHT(1000000 + (CAST(RIGHT(@.LastMemberID,6) AS INT) + 1), 6)
INSERT INTO MemberInfo (MemberID, MemberTitle, MemberFirstName, MemberLastName)
SELECT 'IM'+ CAST(RIGHT(ISNULL(MAX(MemberID),'IM000000'),6) + 1 AS CHAR(6)),
@.parMemberTitle,
@.parMemberFirstName,
@.parMemberLastName
FROM MemberInfo

If you really want to be sure about this you have to specify a TABLOCK.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

Thanks for your suggestion...

Since I have been using mysql for quite a while and thus not familiar with mssql.

what I am really looking for is something similar to "SELECT .... FOR UPDATE" in mysql.

I will try your suggest that later.

Thanks