34,838
社区成员




CREATE FUNCTION GetMax(@table_name varchar(50) , @column_name varchar(50))
RETURNS int
AS
Begin
Declare @max_value int
SELECT @max_value = MAX(@column_name) + 1 from @table_nam
if @max_value is not NULL return 0
Return @max_value
End
CREATE TABLE dbo.tableColumnMaxValue(
tableName VARCHAR(50) NOT NULL,
columnName VARCHAR(50) NOT NULL,
maxValue INT NOT NULL,
CONSTRAINT PK_tableColumnMaxValue PRIMARY KEY(
tableName,
columnName
)
)
DECLARE @table_name VARCHAR(50) ,
@column_name VARCHAR(50) ,
@max_value INT;
EXEC GetMax @table_name, @column_name, @max_value OUTPUT;
INSERT into TableLocation values(@max_value,1,10,20,GETDATE())
insert into TableLocation values(dbo.GetMax('TableLocation','pos_id'),1,10,20,GETDATE())
消息 4121,级别 16,状态 1,第 1 行
找不到列 "dbo" 或用户定义的函数或聚合 "dbo.GetMax",或者名称不明确。
CREATE PROCEDURE GetMax
(
@table_name VARCHAR(50) ,
@column_name VARCHAR(50) ,
@max_value INT = 0 OUTPUT
)
AS
BEGIN
DECLARE @Sql NVARCHAR(1000);
SET @Sql = 'SELECT @max_value = MAX(' + @column_name + ') + 1 from '
+ @table_name;
EXEC sys.sp_executesql @Sql, N'@max_value int output',
@max_value OUTPUT;
IF @max_value IS NULL
SET @max_value = 0;
END;
GO
DECLARE @table_name VARCHAR(50) ,
@column_name VARCHAR(50) ,
@max_value INT;
EXEC GetMax @table_name, @column_name, @max_value OUTPUT;
SELECT @max_value;
IF @max_value is not NULL
set @max_value = 0
问题就剩下表名了。