declare @maxid varchar(1000)
declare @str nvarchar(4000)
set @str = 'select @maxid = ''1'''
exec sp_executesql @str,N'@maxid varchar(1000) output',@maxid output
select @maxid
declare @str nvarchar(4000)
set @str = 'select @maxid = ''1'''
exec sp_executesql @str,N'@maxid varchar(1000) output',@maxid output
select @maxid
declare @idcolname varchar(1000)
declare @str3 varchar(8000)
set @tabname='aaa'
set @idcolname='ccc'
set @str3 = 'declare @t_b table(id bigint)
select b.id from @t_b as b where not exists(select a.'+@idcolname+' from '+@tabname+' as a where a.'+@idcolname+' = b.id)'
exec(@str3)
-- create table t_a(id bigint not null)
-- insert into t_a
-- select 0001
-- UNION ALL select 0011
-- UNION ALL select 0004
-- UNION ALL select 0002
-- UNION ALL select 0008
--
-- select * from t_a
/*存储过程(仍有问题,请指正)*/
alter procedure RtnKyId
(
@tabname varchar(1000),
@idcolname varchar(1000)
)
as
begin
declare @num bigint
declare @maxid varchar(8000)
-- declare @t_b table(id bigint)
declare @str1 nvarchar(4000)
declare @str2 varchar(8000)
declare @str3 varchar(8000) set @num = 0
set @str1 = 'select '+@maxid+' = max('+@idcolname+') from '+@tabname
exec sp_executesql @str1,N'@maxid varchar(8000) output',@maxid output
select @maxid
while @num < @maxid
begin
set @num = @num + 1
set @str2 = 'declare @t_b table(id bigint) insert into @t_b select '+@num
exec(@str2)
end
set @str3 = 'declare @t_b table(id bigint)
select b.id from @t_b as b
where not exists(select a.'+@idcolname+' from '+@tabname+' as a where a.'+@idcolname+' = b.id)'
exec(@str3)
end
go
exec RtnKyId 't_a','id'
--执行结果
------------------------------
NULL(所影响的行数为 1 行)id
-------------------- (所影响的行数为 0 行)
——》
declare @maxid bigint--与表中类型要一致 set @str1 = 'select '+@maxid+' = max('+@idcolname+') from '+@tabname
exec sp_executesql @str1,N'@maxid varchar(8000) output',@maxid output
——》
set @str1 = 'select @maxid = max('+@idcolname+') from '+@tabname--@maxid在引号里面
exec sp_executesql @str1,N'@maxid bigint output',@maxid output--注意@maxid的类型
alter procedure RtnKyId
(
@tabname varchar(1000),
@idcolname varchar(1000)
)
as
begin
declare @num bigint
declare @maxid bigint
-- declare @t_b table(id bigint)
declare @str1 nvarchar(4000)
declare @str2 varchar(8000)
declare @str3 varchar(8000) set @num = 0
set @str1 = 'select @maxid=max('+@idcolname+') from '+@tabname
exec sp_executesql @str1,N'@maxid bigint output',@maxid output
create table #t_b(id bigint)
while @num < @maxid
begin
set @num = @num + 1
set @str2 = 'insert into #t_b select '+cast(@num as varchar)
exec(@str2)
end
set @str3 = 'select b.id from #t_b as b
where not exists(select a.'+@idcolname+' from '+@tabname+' as a where a.'+@idcolname+' = b.id)'
exec(@str3)
end
go
exec RtnKyId 't_a','id'
用临时表,创建删除会使日志量增大,并且在存储过程中使用表变量与使用临时表相比,减少了存储过程的重新编译量。。正是如此,我才使用表变量的,但是我语句不精,总报错,请赐教:)
alter procedure RtnKyId
(
@tabname varchar(1000),
@idcolname varchar(8000)
)
as
begin
declare @num bigint
declare @maxid bigint -- 与表@tabname中类型要一致
-- declare @t_b table(id bigint)
declare @str1 nvarchar(4000)
declare @str2 varchar(8000)
declare @str3 varchar(8000) set @num = 0
set @str1 = 'select @maxid=max('+@idcolname+') from '+@tabname
exec sp_executesql @str1,N'@maxid bigint output',@maxid output
-- create table #t_b(id bigint)
while @num < @maxid
begin
set @num = @num + 1
set @str2 = 'declare @t_b table(id bigint) insert into @t_b select '+cast(@num as varchar)
exec(@str2)
end
set @str3 = 'declare @t_b table(id bigint) select b.id from @t_b as b
where not exists(select a.'+@idcolname+' from '+@tabname+' as a where a.'+@idcolname+' = b.id)'
exec(@str3)
-- select * from @t_b
end
go
exec RtnKyId 't_a','id'
alter procedure RtnKyId
(@tabname varchar(1000),@idcolname varchar(8000))
as
begin
declare @maxid bigint
declare @str1 nvarchar(4000)
set @str1 = 'select @maxid=max('+@idcolname+') from '+@tabname
exec sp_executesql @str1,N'@maxid bigint output',@maxid output
set @str1='select top '+cast(@maxid as varchar)+' identity(int,1,1) id into #t_b from syscolumns a,syscolumns b,syscolumns c '
set @str1 =@str1+ 'select b.id from #t_b as b
where not exists(select a.'+@idcolname+' from '+@tabname+' as a where a.'+@idcolname+' = b.id)'
exec(@str1)
end
go
exec RtnKyId 't_a','id'
syscolumns a,syscolumns b,syscolumns c 不解?