IDENTITY函数内是否不支持使用变量?大家是如何解决的?
----------------
declare @t int
set @t=100select id=IDENTITY(int, @t, 1),* into #t1 from t
select * from #t1
----------------
declare @t int
set @t=100select id=IDENTITY(int, @t, 1),* into #t1 from t
select * from #t1
declare @t int
declare @s varchar(500)
set @t=100
set @s = 'select id=IDENTITY(int, '+convert(varchar(10),@t)+', 1),* into ##t1 from texec(@s)
select * from ##t1
declare @t int
declare @s varchar(500)
set @t=100
set @s = 'select id=IDENTITY(int, '+convert(varchar(10),@t)+', 1),* into ##t1 from t' --刚少了个单引号exec(@s)
select * from ##t1
-----------
declare @s varchar(100)
set @s='select 1 from t'
exec @s
还有临时表在这里要用##t1而用#t1不行又是为什么?非常感谢
declare @s varchar(500)
set @t=100
set @s = 'select id=IDENTITY(int, '+convert(varchar(10),@t)+', 1),* into ##t1 from texec(@s)
select * from ##t1谢谢,另外能说说exec(@s)和sp_executesql的区别吗?
sp_executesql 支持参数替换 可重用执行计划
##是全局的 # 是局部的所以他用##
declare @s varchar(500)
set @t=100
set @s = 'select id=IDENTITY(int, '+convert(varchar(10),@t)+', 1),* into #t1 from t;
select * from #t1' --刚少了个单引号exec(@s)
建议写成这样
declare @t int
declare @s varchar(500)
set @t=100
set @s = 'select id=IDENTITY(int, '+convert(varchar(10),@t)+', 1),* into #t1 from tb;
select * from #t1' --刚少了个单引号exec(@s)全局临时表少用
用的话并发不好
set @t=100 if object_id('tempdb..#t1') is not null
drop table #t1
create table #t1(ID int identity,col1 int)
dbcc checkident('Tempdb..#t1',reseed,@t)insert #T1
select top 5 ID from sysobjectsselect * from #t1
/*
ID col1
----------- -----------
100 4
101 5
102 7
103 8
104 13
*/
declare @s varchar(500)
set @t=100
set @s ='select id=identity(int,'+cast(@t as nvarchar)+',1), * into #t1 from t select * from #t1'
exec(@s)