create procedure dep_zixun
(@Dep nvarchar(20),
@project nvarchar(20),
@StartDate nvarchar(20),
@EndDate nvarchar(20))
As Select * into #tmp from usr_incidents where ts_subdep=@Dep and and ts_project in (@project) and ts_submitdate between @StartDate and @EndDate
select
a.ts_name, count(ts_questiontype)as 个数,cast(ceiling(count(ts_questiontype)*1.0/(select count(1) from QD_incidents_zx )*100) as varchar(3))+'%' as 百分比 from ts_selections a left join #tmp b on a.ts_id=b.ts_questiontype
where a.ts_fldid=147
group by b.ts_questiontype,a.ts_namedrop table #tmp
exec dep_zixun 106,'53,54,55,56,57',1142817462,1144029122
执行时报错?这该怎么办?
(@Dep nvarchar(20),
@project nvarchar(20),
@StartDate nvarchar(20),
@EndDate nvarchar(20))
As Select * into #tmp from usr_incidents where ts_subdep=@Dep and and ts_project in (@project) and ts_submitdate between @StartDate and @EndDate
select
a.ts_name, count(ts_questiontype)as 个数,cast(ceiling(count(ts_questiontype)*1.0/(select count(1) from QD_incidents_zx )*100) as varchar(3))+'%' as 百分比 from ts_selections a left join #tmp b on a.ts_id=b.ts_questiontype
where a.ts_fldid=147
group by b.ts_questiontype,a.ts_namedrop table #tmp
exec dep_zixun 106,'53,54,55,56,57',1142817462,1144029122
执行时报错?这该怎么办?
解决方案 »
- [+急]java中关于重复关闭记录集rs,stmt的疑惑?
- SQL Server里的TimeStamp是如何使用的?与Binary(8)又是什么关系?
- 请教updata,谢谢
- 想用表存放图的邻接矩阵,问这个表该怎么设计最合理?
- 如何用语句实现以下功能(思路可行马上给分)
- 读取ACCESS遇到的一个奇怪问题
- 求教:公司正在调试自己编的软件,要求输入10000条商品信息,现在已经输了1000条了,我想请问有什么办法能够复制已输入好的1000条10次从而达
- 请教小问题......
- 有哪位高手指点一下!(在线等待回复!谢谢!)
- 数据库实际问题,急求
- 求一条如何再插入语句中,使纪录序号自动加1,谢谢
- 怎么样生成含数据的sql脚本?
alter procedure dep_zixun
(@Dep nvarchar(20),
@project nvarchar(20),
@StartDate nvarchar(20),
@EndDate nvarchar(20))
As Select * into #tmp from usr_incidents
where ts_subdep=@Dep and ts_project in (@project) and
ts_submitdate between @StartDate and @EndDate select a.ts_name, count(ts_questiontype)as 个数,
cast(ceiling(count(ts_questiontype)*1.0/(select count(1)
from QD_incidents_zx )*100) as varchar(3))+'%' as 百分比 from ts_selections a
left join #tmp b on a.ts_id=b.ts_questiontype
where a.ts_fldid=147
group by b.ts_questiontype,a.ts_namedrop table #tmp
go
exec dep_zixun 106,'53,54,55,56,57',1142817462,1144029122
create procedure dep_zixun
(@Dep nvarchar(20),
@project nvarchar(20),
@StartDate nvarchar(20),
@EndDate nvarchar(20))
Asexec('Select * into #tmp from usr_incidents where ts_subdep=@Dep and and ts_project in ('+@project+') and ts_submitdate between @StartDate and @EndDate')
select
a.ts_name,count(ts_questiontype)as 个数,cast(ceiling(count(ts_questiontype)*1.0/(select count(1) from QD_incidents_zx )*100) as varchar(3))+'%' as 百分比 from ts_selections a left join #tmp b on a.ts_id=b.ts_questiontype
where a.ts_fldid=147
group by b.ts_questiontype,a.ts_namedrop table #tmp
--------------------------------------------------------------------------------动态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
alter procedure dep_zixun
(@Dep nvarchar(20),
@project nvarchar(20),
@StartDate nvarchar(20),
@EndDate nvarchar(20))
Asexec('
Select * into #tmp from usr_incidents
where ts_subdep='+@Dep+' and ts_project in ('+@project+') and
ts_submitdate between '''+@StartDate+''' and '''+@EndDate+''' select a.ts_name, count(ts_questiontype)as 个数,
cast(ceiling(count(ts_questiontype)*1.0/(select count(1)
from QD_incidents_zx )*100) as varchar(3))+''%'' as 百分比 from ts_selections a
left join #tmp b on a.ts_id=b.ts_questiontype
where a.ts_fldid=147
group by b.ts_questiontype,a.ts_namedrop table #tmp ')go
将 nvarchar 值 '53,54,55,56,57' 转换为数据类型为 int 的列时发生语法错误。