--在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 aid ----------- 1 2
/* 在TOP后面使用变量 (爱新觉罗.毓华 2008-01-02 广东深圳) */--SQL SERVER 2005 的写法 use adventureworks goDECLARE @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 */
动态SQLdeclare @sql varchar(500),@n int select @n=1 select @sql= 'select top '+@n +' * from table' exec(@sql)
--sql server 2005 对 top n 的改进写法 declare @n int set @n=10 select top (@n) * from sys.objects
大家再帮我一下,我又加了一个条件句: 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
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 aid
-----------
1
2
在TOP后面使用变量
(爱新觉罗.毓华 2008-01-02 广东深圳)
*/--SQL SERVER 2005 的写法
use adventureworks
goDECLARE @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
*/
select @n=1
select @sql= 'select top '+@n +' * from table'
exec(@sql)
--sql server 2005 对 top n 的改进写法
declare @n int
set @n=10
select top (@n) * from sys.objects
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