我想用自定义函数生成ID
输入参数:表格名,id列名
输出:新的ID
两种都编译不通过,语法错误,请指教,究竟错在哪里了?谢谢----方法一
CreateFunction dbo.GenerateID(@TableName varchar,@IDCol varchar)
returnvarchar
as
begin
declare @idreturn asvarchar(10)
select @idreturn =max(@IDCol)+1 from @TableName
return @idreturn
end---方法二
go
CreateFunction dbo.GenerateID2(@TableName varchar,@IDCol varchar)
returnvarchar
as
begin
declare sql0 asvarchar(1000);
declare @idreturn asvarchar(10)
sql0='select max('+@IDCol+')+1 from '+@TableName
set @idreturn=exec(sql0)
return @idreturn
end
输入参数:表格名,id列名
输出:新的ID
两种都编译不通过,语法错误,请指教,究竟错在哪里了?谢谢----方法一
CreateFunction dbo.GenerateID(@TableName varchar,@IDCol varchar)
returnvarchar
as
begin
declare @idreturn asvarchar(10)
select @idreturn =max(@IDCol)+1 from @TableName
return @idreturn
end---方法二
go
CreateFunction dbo.GenerateID2(@TableName varchar,@IDCol varchar)
returnvarchar
as
begin
declare sql0 asvarchar(1000);
declare @idreturn asvarchar(10)
sql0='select max('+@IDCol+')+1 from '+@TableName
set @idreturn=exec(sql0)
return @idreturn
end
http://blog.csdn.net/roy_88/archive/2006/12/01/1424370.aspx
returnvarchar
as
begin
declare @idreturn asvarchar(10)
select @idreturn =max(@IDCol)+1 from @TableName--你这句是想干什么?
--max(@IDCol)是想干啥?
return @idreturn
end
@TableName SYSNAME,
@IDCol SYSNAME,
@idreturn INT OUTPUT
AS
BEGIN
DECLARE @s NVARCHAR(4000)
SET @s='SELECT @idreturn = MAX(['+@IDCol+'])+1
FROM ['+@TableName+'] WITH(XLOCK,PAGLOCK)'
EXEC sp_executesql @s,N'@idreturn INT OUT',@idreturn OUT
END
GO
--调用实例:DECLARE @idreturn INT
EXEC P_GenerateID 表名,字段名,@idreturn OUT
SELECT @idreturn
@TableName nvarchar,
@IDCol nVARCHAR,
@ID INT OUTPUT
)
AS
BEGIN
DECLARE @s NVARCHAR(4000)
SET @s=N'select @ID =isnull(max('+@IDCol+'),0)+1 from '+@TableName
EXEC sp_executesql @s,N'@ID int output',@ID output
end
GO
DECLARE @ID INT
EXEC GenerateID ,@TableName='',@IDCol='',@ID OUTPUT
SELECT @ID
@TableName SYSNAME,
@IDCol SYSNAME,
@idreturn INT OUTPUT
AS
BEGIN
DECLARE @s NVARCHAR(4000)
SET @s='SELECT @idreturn = ISNULL(MAX(['+@IDCol+'])+1,1)
FROM ['+@TableName+'] WITH(XLOCK,PAGLOCK)'
EXEC sp_executesql @s,N'@idreturn INT OUT',@idreturn OUT
END
GO
--调用实例:DECLARE @idreturn INT
EXEC P_GenerateID 表名,字段名,@idreturn OUT
SELECT @idreturn嗯,漏考虑了NULL值,修改一下。