select top @STATES id from [table1]

[haya8721]

这句应该如何改下呢,或用其它方法实现呢

[青团子]

exec('select top '+@STATES+' id from [table1] ')

[青团子]

CREATE PROCEDURE selnum_getID
@STATES int
AS
declare @SQL VarChar(8000)
Set @SQL='select top '+CAST(@top As VarChar(20))+' id from [table1]'
Exec (@SQL)
GO

[haya8721]

可是如果
exec('select top '+@STATES+' id into #temp1 from [table1] ')
的话，无法创建#temp1

[futulove]

exec 不能这样top 的,要这样
declare @STATES int
set @STATES=你要的数字
set rowcount=@STATES
select * into #temp1 from table1

[青团子]

嗯？
偶这边运行是成功的呀……

[haya8721]

to : futulove(福途￡爱)
set @STATES=你要的数字
set rowcount=@STATES ---???????
select * into #temp1 from table1
好像没有这种语法吧，能不能说说详细点

to: Cityfire(青团子)
select top 2 * from [table1] where [id] in (select top @STATES id from [table1] order by dt) order by dt desc
我的这个子查询，主要功能是选出经过排序后特定位置的两条记录，变量@states标明的是位置,用刚才那个exec('select top '+@STATES+' id from [table1] ')确实运行是正确的，w但是我这边用不上，所以我想到先把exec('select top '+@STATES+' id from [table1] ')的结果存进一张临时表，因此想用exec('select top '+@STATES+' id into #temp1 from [table1] ')来实现，但是好像无法创建对象
我感觉我这个方法笨了点，有没有其它的方法呢，能不能指点一二

[青团子]

create table #tmp1(ID int)
exec('insert into #tmp1 select top '+@STATES+' id from [table1] ')
select * from #tmp1

那你这样试试看