declare @i int set @i=5*7 select @iselect top @i id from qcsjb
--1, declare @rownum int set @rownum =5*7 set rowcunt @rownum select id from qcsjb --2, exec('select top '+rtrim(@rownum)+' id from qcsjb')
更正:set rowcount @rownum
--在SQL2005中可以使用: select top(5*7) 35 id from qcsjb
去掉35: select top(5*7) id from qcsjb
select top(5*7) id from qcsjb
--動態 declare @rownum int set @rownum =5*7 exec('select top '+rtrim(@rownum)+' id from qcsjb') 动态sql语句基本语法 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
--2000 declare @num int declare @s varcharset @num = 5 * 7 set @s = 'select top '+convert(varchar(10),@num)+' id from qcsjb' exec(@s) --2005/2008 select top (5*7) id from qcsjb总结.
--2000 declare @num int declare @s varchar(100) --修正,整理少定义长度了set @num = 5 * 7 set @s = 'select top '+convert(varchar(10),@num)+' id from qcsjb' exec(@s) --2005/2008 select top (5*7) id from qcsjb
--在top后面使用变量declare @num as int declare @sql as varchar(2000) set @num = 5 * 7 set @sql = 'select top ' + cast(@num as char) + ' * from tb' exec(@sql)
/* 在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 */
set @i=5*7
select @iselect top @i id from qcsjb
--1,
declare @rownum int
set @rownum =5*7
set rowcunt @rownum
select id from qcsjb
--2,
exec('select top '+rtrim(@rownum)+' id from qcsjb')
select top(5*7) 35 id from qcsjb
去掉35:
select top(5*7) id from qcsjb
declare @rownum int
set @rownum =5*7
exec('select top '+rtrim(@rownum)+' id from qcsjb')
动态sql语句基本语法
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
--2000
declare @num int
declare @s varcharset @num = 5 * 7
set @s = 'select top '+convert(varchar(10),@num)+' id from qcsjb'
exec(@s)
--2005/2008
select top (5*7) id from qcsjb总结.
--2000
declare @num int
declare @s varchar(100) --修正,整理少定义长度了set @num = 5 * 7
set @s = 'select top '+convert(varchar(10),@num)+' id from qcsjb'
exec(@s)
--2005/2008
select top (5*7) id from qcsjb
--在top后面使用变量declare @num as int
declare @sql as varchar(2000)
set @num = 5 * 7
set @sql = 'select top ' + cast(@num as char) + ' * from tb'
exec(@sql)
在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
*/