ALTER procedure [dbo].[Common_SelectCode]
@basicCode nvarchar(3), ----基础编码
@tableName nvarchar(20), ----表名
@key nvarchar(20) ----关键字段
as
begin
declare @s char(13), ----最后的编码
@sd char(8), ----日期编码
@ms char(13), ----当天最大编码
@si char(2) ----
select @sd = replace(convert(varchar(10),getdate(),120),'-','')
---select @sd = convert(varchar(10),getdate(),112)
--if exists (select 1 from Reports where RepRkey = 'Rep'+ @sd + '01')
if exists (select 1 from @tableName where @key = @basicCode + @sd + '01')
begin
select @ms = max(@key) from @tableName where substring(@key,4,8) = @sd
select @si = right('0' + convert(varchar(2), convert(tinyint,right(@ms,2)) + 1),2)
select @s = left(@ms,len(@ms)-2)+@si
end
else
begin
--select @s = 'Rep'+ @sd + '01'
select @s = @basicCode + @sd + '01'
endselect @s as NewRkey
end-------------------------------
报错:
消息 1087,级别 16,状态 1,过程 Common_SelectCode,第 16 行
必须声明表变量 "@tableName"。
消息 1087,级别 16,状态 1,过程 Common_SelectCode,第 18 行
必须声明表变量 "@tableName"。
@basicCode nvarchar(3), ----基础编码
@tableName nvarchar(20), ----表名
@key nvarchar(20) ----关键字段
as
begin
declare @s char(13), ----最后的编码
@sd char(8), ----日期编码
@ms char(13), ----当天最大编码
@si char(2) ----
select @sd = replace(convert(varchar(10),getdate(),120),'-','')
---select @sd = convert(varchar(10),getdate(),112)
--if exists (select 1 from Reports where RepRkey = 'Rep'+ @sd + '01')
if exists (select 1 from @tableName where @key = @basicCode + @sd + '01')
begin
select @ms = max(@key) from @tableName where substring(@key,4,8) = @sd
select @si = right('0' + convert(varchar(2), convert(tinyint,right(@ms,2)) + 1),2)
select @s = left(@ms,len(@ms)-2)+@si
end
else
begin
--select @s = 'Rep'+ @sd + '01'
select @s = @basicCode + @sd + '01'
endselect @s as NewRkey
end-------------------------------
报错:
消息 1087,级别 16,状态 1,过程 Common_SelectCode,第 16 行
必须声明表变量 "@tableName"。
消息 1087,级别 16,状态 1,过程 Common_SelectCode,第 18 行
必须声明表变量 "@tableName"。
EXEC('select 1 from '+@tableName+' where '+@key +' =''' +@basicCode+@sd+'01''')
IF @@ROWCOUNT>0
BEGIN
SET @sql=N'select @ms = max('+@key+') from '+@tableName+' where substring('+@key+',4,8) = '''+@sd+''''
exec sp_executesql @sql,N'@ms char(13) out',@ms OUT
select @si = right('0' + convert(varchar(2), convert(tinyint,right(@ms,2)) + 1),2)
select @s = left(@ms,len(@ms)-2)+@si
END