SQL 前 n条记录

[q77028578]

谁知道 取一个表的前n条记录，n是变量的存储过程怎么写？
select top n * from 。。。 这样语法错误，谁还有别的办法？

[kk19840210]

declare @n int
set @n=50

exec('select top '+convert(varchar(10),@n)+' * from tab')

[kk19840210]

动态语句拼接

[dawugui]

--在top后面使用变量
create table a([id] [int])
insert into a(id) values(1)
insert into a(id) values(2)
insert into a(id) values(3)
insert into a(id) values(4)
insert into a(id) values(5)

declare @num as int
declare @sql as varchar(2000)
set @num = 2
set @sql = 'select top ' + cast(@num as char) + ' * from a'
exec(@sql)

drop table a

id
-----------
1
2

[dawugui]

/*
在TOP后面使用变量
(爱新觉罗.毓华  2008-01-02  广东深圳)
*/

--SQL SERVER 2005 的写法
use adventureworks
go

DECLARE @Percentage int
SET @Percentage = 1
SELECT TOP (@Percentage) PERCENT
Name
FROM Production.Product
ORDER BY Name

/*
Name
----------------------
Adjustable Race
All-Purpose Bike Stand
AWC Logo Cap
BB Ball Bearing
Bearing Ball
Bike Wash - Dissolver

(6 行受影响)
*/

-----------------------------------
--SQL SERVER 2000 的写法
create table a([id] [int])
insert into a(id) values(1)
insert into a(id) values(2)
insert into a(id) values(3)
insert into a(id) values(4)
insert into a(id) values(5)

declare @num as int
declare @sql as varchar(2000)
set @num = 2
set @sql = 'select top ' + cast(@num as char) + ' * from a'
exec(@sql)

drop table a
/*
id          
----------- 
1
2
*/

[utpcb]

动态SQL

declare @sql varchar(500),@n int
select @n=1
select @sql= 'select top '+@n +' * from table'
exec(@sql)

[utpcb]

小虫 大乌龟真快

[zlp321002]

--sql server 2005 对 top n 的改进写法
declare @n int
set @n=10
select top (@n) * from sys.objects

[q77028578]

大家再帮我一下，我又加了一个条件句：
declare @sql as varchar(2000)
set @sql ='select top '+ cast(@num as char)+ ' * from chitchat where insertdate >= '+ convert(varchar(8),@time,112) +' ORDER BY insertdate DESC '
exec(@sql)

但是有错误：
服务器: 消息 8115，级别 16，状态 2，行 1
将 expression 转换为数据类型 datetime 时发生算术溢出错误。
存储过程: SpyWap.dbo.SpyWap_MoreChatMsg
返回代码 = 0