/*
在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
*/
在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
*/
*@jcfw_code:基层服务订单抽取数据抽取百分比率*/
declare @jcfw_code int;
set @jcfw_code=100;
select @jcfw_code=arg_value from sys_arg where code='jcfw_code';/*@project_code:服务超市、家政项目订单抽取百分比率*/
declare @project_code int;
set @project_code=0;
select @project_code=arg_value from sys_arg where code='12';/*服务超市、家政项目订单抽取数据*/
exec('
select top '+rtrim(@project_code)+' percent a.* from (
select id,post_time,subclassid,post_user_id,post_area_id from hot_order where classid=157 and post_time<getdate()
and post_time>cast(year(getdate()) as varchar(4))+''-''+cast(month(getdate()) as varchar(2))+''-''+cast((day(getdate())-2) as varchar(2))
)a
inner join project b
on a.SUBCLASSID=b.id and b.ISHOT=1
union
select a.* from (
select id,post_time,SUBCLASSID,post_user_id,post_area_id from hot_order where classid=157 and post_time<getdate()
and post_time>cast(year(getdate()) as varchar(4))+''-''+cast(month(getdate()) as varchar(2))+''-''+cast((day(getdate())-2) as varchar(2))
)a
inner join project b
on a.SUBCLASSID=b.id and b.ISHOT=-1
/*基层服务订单抽取数据*/
union
select top 60 percent a.* from (
select id,post_time,subclassid,post_user_id,post_area_id from hot_order where classid=159 and post_time<getdate()
and post_time>cast(year(getdate()) as varchar(4))+''-''+cast(month(getdate()) as varchar(2))+''-''+cast((day(getdate())-2) as varchar(2))
)a')
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
declare @jcfw_code int;
set @jcfw_code=100;
select @jcfw_code=arg_value from sys_arg where code='jcfw_code';/*@project_code:服务超市、家政项目订单抽取百分比率*/
declare @project_code int;
set @project_code=0;
select @project_code=arg_value from sys_arg where code='12';declare @sql varchar(8000)/*服务超市、家政项目订单抽取数据*/
set @sql='
select top '+rtrim(@project_code)+' percent a.* from (
select id,post_time,subclassid,post_user_id,post_area_id from hot_order where classid=157 and post_time<getdate()
and post_time>cast(year(getdate()) as varchar(4))+''-''+cast(month(getdate()) as varchar(2))+''-''+cast((day(getdate())-2) as varchar(2))
)a
inner join project b
on a.SUBCLASSID=b.id and b.ISHOT=1
union
select a.* from (
select id,post_time,SUBCLASSID,post_user_id,post_area_id from hot_order where classid=157 and post_time<getdate()
and post_time>cast(year(getdate()) as varchar(4))+''-''+cast(month(getdate()) as varchar(2))+''-''+cast((day(getdate())-2) as varchar(2))
)a
inner join project b
on a.SUBCLASSID=b.id and b.ISHOT=-1
/*基层服务订单抽取数据*/
union
select top 60 percent a.* from (
select id,post_time,subclassid,post_user_id,post_area_id from hot_order where classid=159 and post_time<getdate()
and post_time>cast(year(getdate()) as varchar(4))+''-''+cast(month(getdate()) as varchar(2))+''-''+cast((day(getdate())-2) as varchar(2))
)a'exec(@sql)
declare @jcfw_code int;
set @jcfw_code=100;
select @jcfw_code=arg_value from sys_arg where code='jcfw_code';/*@project_code:服务超市、家政项目订单抽取百分比率*/
declare @project_code int;
set @project_code=0;
select @project_code=arg_value from sys_arg where code='12';/*服务超市、家政项目订单抽取数据*/
exec('
select top ' + cast(@project_code as varchar) + ' percent a.* from (
select id,post_time,subclassid,post_user_id,post_area_id from hot_order where classid=157 and post_time<getdate()
and post_time>cast(year(getdate()) as varchar(4))+''-''+cast(month(getdate()) as varchar(2))+''-''+cast((day(getdate())-2) as varchar(2))
)a
inner join project b
on a.SUBCLASSID=b.id and b.ISHOT=1
union
select a.* from (
select id,post_time,SUBCLASSID,post_user_id,post_area_id from hot_order where classid=157 and post_time<getdate()
and post_time>cast(year(getdate()) as varchar(4))+''-''+cast(month(getdate()) as varchar(2))+''-''+cast((day(getdate())-2) as varchar(2))
)a
inner join project b
on a.SUBCLASSID=b.id and b.ISHOT=-1
/*基层服务订单抽取数据*/
union
select top 60 percent a.* from (
select id,post_time,subclassid,post_user_id,post_area_id from hot_order where classid=159 and post_time<getdate()
and post_time>cast(year(getdate()) as varchar(4))+''-''+cast(month(getdate()) as varchar(2))+''-''+cast((day(getdate())-2) as varchar(2))
)a')