我想做一个简单的分页的查询,为什么查询的每页记录数和当前页数不能用变量动态指定?语句如下所示:
use pubs
goselect top 5 * from titles
where title_id not in(select top 10 title_id from titles)
go
这个语句没问题,可以显示查询的记录。select top 5 * from titles
where title_id not in(select top 2*5 title_id from titles)
go
这个语句仅是把 10 用 2*5 进行代替,就报了以下这个错误:
第 2 行: '5' 附近有语法错误declare @pageSize int
declare @curPage int
select @pageSize=5,@curPage=3select top @pageSize * from titles
where title_id not in(select top (@curPage-1)*@pageSize title_id from titles)
go
这个语句用变量来代替数字,报以下的错误信息:
服务器: 消息 170,级别 15,状态 1,行 5
第 5 行: '@pageSize' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 6
第 6 行: '(' 附近有语法错误。
我的目的是最终写一个存储过程,而这里的语句中变量始终过不去,请问究竟错在哪里?
use pubs
goselect top 5 * from titles
where title_id not in(select top 10 title_id from titles)
go
这个语句没问题,可以显示查询的记录。select top 5 * from titles
where title_id not in(select top 2*5 title_id from titles)
go
这个语句仅是把 10 用 2*5 进行代替,就报了以下这个错误:
第 2 行: '5' 附近有语法错误declare @pageSize int
declare @curPage int
select @pageSize=5,@curPage=3select top @pageSize * from titles
where title_id not in(select top (@curPage-1)*@pageSize title_id from titles)
go
这个语句用变量来代替数字,报以下的错误信息:
服务器: 消息 170,级别 15,状态 1,行 5
第 5 行: '@pageSize' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 6
第 6 行: '(' 附近有语法错误。
我的目的是最终写一个存储过程,而这里的语句中变量始终过不去,请问究竟错在哪里?
declare @pageSize int
declare @curPage int
select @pageSize=5,@curPage=3
declare @i int
set @i=(@curPage-1)*@pageSize
exec('select top '+@pageSize +' * from titles where title_id not in(select top '+@i+' title_id from titles)')
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
select top 5 * from titles
where title_id not in(select top (2*5) title_id from titles)
go
declare @pageSize int,@curPage int
select @pageSize=5,@curPage=3
declare @s varchar(8000)
set @s='select top '+ltrim(@pageSize)+' * from titles
where title_id not in(select top ( '+ltrim((@curPage -1)*@pageSize)+') title_id from titles) '
exec(@s)
go
declare @s varchar(8000)
set @s='select top 5 * from titles
where title_id not in(select top '+ltrim(2*5)+' title_id from titles) '
exec(@s)
go
declare @pageSize int,@curPage int
select @pageSize=5,@curPage=3
declare @s varchar(8000)
set @s='select top '+ltrim(@pageSize)+' * from titles
where title_id not in(select top '+ltrim((@curPage -1)*@pageSize)+' title_id from titles) '
exec(@s)
go
declare @p int
set @p=2*5
exec ('select top 5 * from titles
where title_id not in(select top '+@p+' title_id from titles)')
在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 @pageSize=5,@curPage=3
declare @s varchar(8000)
select top (@pageSize) * from titles
where title_id not in(select top ((@curPage -1)*@pageSize) title_id from titles)
exec(@s)
go /*
title_id title type pub_id price advance royalty ytd_sales notes pubdate
-------- -------------------------------------------------------------------------------- ------------ ------ --------------------- --------------------- ----------- ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------
PS1372 Computer Phobic AND Non-Phobic Individuals: Behavior Variations psychology 0877 21.59 7000.00 10 375 A must for the specialist, this book examines the difference between those who hate and fear computers and those who don't. 1991-10-21 00:00:00.000
PS2091 Is Anger the Enemy? psychology 0736 10.95 2275.00 12 2045 Carefully researched study of the effects of strong emotions on the body. Metabolic charts included. 1991-06-15 00:00:00.000
PS2106 Life Without Fear psychology 0736 7.00 6000.00 10 111 New exercise, meditation, and nutritional techniques that can reduce the shock of daily interactions. Popular audience. Sample menus included, exercise video available separately. 1991-10-05 00:00:00.000
PS3333 Prolonged Data Deprivation: Four Case Studies psychology 0736 19.99 2000.00 10 4072 What happens when the data runs dry? Searching evaluations of information-shortage effects. 1991-06-12 00:00:00.000
PS7777 Emotional Security: A New Algorithm psychology 0736 7.99 4000.00 10 3336 Protecting yourself and your loved ones from undue emotional stress in the modern world. Use of computer and nutritional aids emphasized. 1991-06-12 00:00:00.000(5 行受影响)
*/
直接加括号就好了
declare @pageSize int
declare @curPage int
select @pageSize=5,@curPage=3 select top (@pageSize) * from titles
where title_id not in(select top ((@curPage-1)*@pageSize) title_id from titles)
go