帮改写个简单的SQL 语句解决办法

帮改写个简单的SQL 语句
Create   Proc   AA
AS  
declare   @MaxID   int
set     EXECUTE   @MaxID   =   alloc_id   "userinfo "


INSERT   INTO   userinfo(id)
VALUES   (@MaxID)
------------------------------------------------
--我想用alloc_id存储过程取出表中的最大ID
------------------------------------------------
ALTER     PROCEDURE   dbo.alloc_id
@tbname VARCHAR(32)
AS
BEGIN
BEGIN   TRANSACTION
UPDATE   table_alloc_id   SET   id   =   id   +   1   WHERE   tablename   =   @tbname
IF   @@rowcount   =   0
BEGIN
INSERT   INTO   table_alloc_id   VALUES(@tbname,   1)
END
SELECT   id   FROM   table_alloc_id   WHERE   tablename   =   @tbname
COMMIT   TRANSACTION
END
-------------------------
为什么@MaxID是0呢？请问该如何改写alloc_id   呢？

------解决方案--------------------
ALTER PROCEDURE dbo.alloc_id
@tbname VARCHAR(32)
AS
BEGIN
BEGIN TRANSACTION
declare @i int--------添加
UPDATE table_alloc_id SET id = id + 1 WHERE tablename = @tbname
IF @@rowcount = 0
BEGIN
INSERT INTO table_alloc_id VALUES(@tbname, 1)
END
SELECT @i=id FROM table_alloc_id WHERE tablename = @tbname
return @i-----------------添加
COMMIT TRANSACTION
END
------解决方案--------------------
ALTER PROCEDURE dbo.alloc_id
@tbname VARCHAR(32)
AS

SET NOCOUNT ON
SET XACT_ABORT ON
declare @i int--------添加
UPDATE table_alloc_id SET id = id + 1 WHERE tablename = @tbname
IF @@rowcount = 0
BEGIN
INSERT INTO table_alloc_id VALUES(@tbname, 1)
END
SELECT @i=id FROM table_alloc_id WHERE tablename = @tbname
return @i-----------------添加
COMMIT TRANSACTION
SET XACT_ABORT OFF
SET NOCOUNT OFF