修改存储过程,帮帮我呀!

[sxl6159078]

1:能不能帮我改一个存储过程.多谢谢.
我的目的是找到一个最大值,然后加1,这步我已经做到了.但是,如果插入一条新的记录时,返回它得到是一个null.

CREATE PROCEDURE GetVllgID
@TownID char(2),
@newVillageID char(4) output
AS
DECLARE @VillageID char(4)
SELECT @VillageID=MAX(cast(VillageID AS integer)) FROM CD_VIllage WHERE TownID=@TownID
SET @VillageID=@VillageID+1
SET @newVillageID=replace(space(4-len(@VillageID)),' ','0')+@VillageID
GO


2:能不能把传进去的参数作为一个字符串,然后在其后补上如:00
如我传进去的参数是:10
我想在其后补上: 1000
^^

[sxl6159078]

插入一条新的记录:是指该表中没有最大值,它得到是一个空值.

[davorsuker39]

SELECT @VillageID=MAX(cast(VillageID AS integer))+1 FROM CD_VIllage WHERE TownID=@TownID

[davorsuker39]

不知道这样可不可以，你定义输入参数改为默认小数点后保留两位！然后replace(参数,',','')

[sxl6159078]

SELECT @VillageID=MAX(cast(VillageID AS integer))+1 FROM CD_VIllage WHERE TownID=@TownID
这句,我放到查询分析器执行一下.还是得到一个空值.
SELECT MAX(cast(VillageID AS integer))+1 FROM CD_VIllage WHERE TownID='10'
上面的兄弟,给出的答案还是得到一个空值.

[zjcxc]

CREATE PROCEDURE GetVllgID
@TownID char(2),
@newVillageID char(4) output
AS
SELECT @newVillageID=right(10001+isnull(MAX(cast(VillageID AS integer)),0),4) FROM CD_VIllage WHERE TownID=@TownID
GO

--调用
declare @newVillageID char(4)
exec GetVllgID 'aa',@newVillageID out

[Andy__Huang]

CREATE PROCEDURE GetVllgID
@TownID varchar(2),
@newVillageID varchar(4) output
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DECLARE @VillageID char(4)
--SELECT @VillageID=MAX(cast(VillageID AS integer))

SELECT @VillageID=ISNULL(MAX(cast(VillageID AS integer))+1,1)
FROM CD_VIllage
WHERE TownID=@TownID
--SET @VillageID=@VillageID+1
SET @newVillageID=replace(space(4-len(@VillageID)),' ','0')+cast(@VillageID as varchar)

return @newVillageID

GO

[hglhyy]

你得到的当然是个NULL, 字段类型没有转换过来！