执行动态SQL时出错!

weifai88 2006-01-04 03:51:48
declare @fmdate datetime,@todate datetime,@mysql varchar(1000)
select @fmdate = '2005-11-01'
select @todate = '2005-11-14'

while @fmdate < @todate

begin


set @mysql = 'sum(case x_wk_pdate when '+@fmdate+' then x_wk_time else 0 end) as '+@fmdate+' ,' + isnull(@mysql,'')

set @fmdate = DATEADD(day, 1, @fmdate)

end

set @mysql = 'select x_gro_id,x_wk_id,' + @mysql + ' from xy_wk_wage group by x_gro_id,x_wk_id '

exec @mysql
go


服务器: 消息 241,级别 16,状态 1,行 10
从字符串转换为 datetime 时发生语法错误。
...全文
124 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
OracleRoob 2006-01-04
  • 打赏
  • 举报
回复

create table xy_wk_wage
(x_gro_id varchar(20),x_wk_id varchar(20),x_wk_pdate datetime,x_wk_wktime dec(10,2) default 0)
go

insert into xy_wk_wage
select '001','a001','2005-11-01',10 union all
select '001','a001','2005-11-02',11 union all
select '001','a001','2005-11-03',9 union all
select '001','a001','2005-11-04',8.5 union all
select '001','a001','2005-11-05',9.5 union all
select '001','a001','2005-11-06',9.5 union all
select '001','a001','2005-11-07',10 union all
select '001','a001','2005-11-08',10 union all
select '001','a001','2005-11-09',10 union all
select '001','a001','2005-11-10',10 union all
select '001','a001','2005-11-11',10 union all
select '001','a001','2005-11-12',10 union all
select '001','a001','2005-11-13',10 union all
select '001','a001','2005-11-14',10 union all
select '001','a002','2005-11-01',10 union all
select '001','a002','2005-11-02',11 union all
select '001','a002','2005-11-03',9 union all
select '001','a002','2005-11-04',8.5 union all
select '001','a002','2005-11-05',9.5 union all
select '001','a002','2005-11-06',9.5 union all
select '001','a002','2005-11-07',10 union all
select '001','a002','2005-11-08',10 union all
select '001','a002','2005-11-09',10 union all
select '001','a002','2005-11-10',10 union all
select '001','a002','2005-11-11',10 union all
select '001','a002','2005-11-12',10 union all
select '001','a002','2005-11-13',10 union all
select '001','a002','2005-11-14',10
go


/*
要求出交叉表
x_gro_id x_wk_id 2005-11-01 2005-11-02 ................
001 a001 10.0 11.0 ....
001 a002 10.0 11.0 ....
*/


declare @fmdate datetime,@todate datetime,@mysql varchar(8000),@aa varchar(20)
select @fmdate = '2005-11-01'
select @todate = '2005-11-14'

while @fmdate < @todate

begin

set @aa = convert(varchar(10),@fmdate,120)

set @mysql = 'sum(case x_wk_pdate when '''+@aa+''' then x_wk_wktime else 0 end) as ['+@aa+'] ,' + isnull(@mysql,' ')

set @fmdate = DATEADD(day, 1, @fmdate)

end

set @mysql = 'select x_gro_id,x_wk_id,' + @mysql + ' 111' + ' from xy_wk_wage group by x_gro_id,x_wk_id '

print @mysql
exec (@mysql)
go


drop table xy_wk_wage

/*
出错信息

第 1 行: '13' 附近有语法错误。
*/
weifai88 2006-01-04
  • 打赏
  • 举报
回复
给出表结构及数据

create table xy_wk_wage
(x_gro_id varchar(20),x_wk_id varchar(20),x_wk_pdate datetime,x_wk_wktime dec(10,2) default 0)


insert into xy_wk_wage
select '001','a001','2005-11-01',10 union all
select '001','a001','2005-11-02',11 union all
select '001','a001','2005-11-03',9 union all
select '001','a001','2005-11-04',8.5 union all
select '001','a001','2005-11-05',9.5 union all
select '001','a001','2005-11-06',9.5 union all
select '001','a001','2005-11-07',10 union all
select '001','a001','2005-11-08',10 union all
select '001','a001','2005-11-09',10 union all
select '001','a001','2005-11-10',10 union all
select '001','a001','2005-11-11',10 union all
select '001','a001','2005-11-12',10 union all
select '001','a001','2005-11-13',10 union all
select '001','a001','2005-11-14',10 union all
select '001','a002','2005-11-01',10 union all
select '001','a002','2005-11-02',11 union all
select '001','a002','2005-11-03',9 union all
select '001','a002','2005-11-04',8.5 union all
select '001','a002','2005-11-05',9.5 union all
select '001','a002','2005-11-06',9.5 union all
select '001','a002','2005-11-07',10 union all
select '001','a002','2005-11-08',10 union all
select '001','a002','2005-11-09',10 union all
select '001','a002','2005-11-10',10 union all
select '001','a002','2005-11-11',10 union all
select '001','a002','2005-11-12',10 union all
select '001','a002','2005-11-13',10 union all
select '001','a002','2005-11-14',10 union all
zlp321002 2006-01-04
  • 打赏
  • 举报
回复
--定义变量过小,最后一个","未处理,建议在执行exe(@mysql)之前,print @mysql 看看,是什么问题,就容易解决了。
--try

declare @fmdate datetime,@todate datetime,@mysql varchar(8000),@aa varchar(20)
select @fmdate = '2005-11-01'
select @todate = '2005-11-14'

while @fmdate < @todate

begin

set @aa = convert(varchar(12),@fmdate)

set @mysql = 'sum(case x_wk_pdate when '+cast(@aa as varchar(10))+' then x_wk_wktime else 0 end) as ['+cast(@aa as varchar(10))+'] ,' + isnull(@mysql,' ')

set @fmdate = DATEADD(day, 1, @fmdate)

end

set @mysql = 'select x_gro_id,x_wk_id,' + left(@mysql,len(@mysql)-1) + ' from xy_wk_wage group by x_gro_id,x_wk_id '

print @mysql

exec (@mysql)
OracleRoob 2006-01-04
  • 打赏
  • 举报
回复



declare @fmdate datetime,@todate datetime,@mysql varchar(1000),@aa varchar(20)
select @fmdate = '2005-11-01'
select @todate = '2005-11-14'

while @fmdate < @todate

begin

set @aa = convert(varchar(12),@fmdate)

set @mysql = 'sum(case x_wk_pdate when '''+@aa+''' then x_wk_wktime else 0 end) as ['+@aa+'] ,' + isnull(@mysql,' ')

set @fmdate = DATEADD(day, 1, @fmdate)

end

set @mysql = 'select x_gro_id,x_wk_id,' + @mysql + ' from xy_wk_wage group by x_gro_id,x_wk_id '

exec (@mysql)
go




--其中下面这一句,第一个@aa两边需要加上单引号,在动态SQL中两个''表示1个
set @mysql = 'sum(case x_wk_pdate when '''+@aa+''' then x_wk_wktime else 0 end) as ['+@aa+'] ,' + isnull(@mysql,' ')

weifai88 2006-01-04
  • 打赏
  • 举报
回复
declare @fmdate datetime,@todate datetime,@mysql varchar(1000),@aa varchar(20)
select @fmdate = '2005-11-01'
select @todate = '2005-11-14'

while @fmdate < @todate

begin

set @aa = convert(varchar(12),@fmdate)

set @mysql = 'sum(case x_wk_pdate when '+@aa+' then x_wk_wktime else 0 end) as ['+@aa+'] ,' + isnull(@mysql,' ')

set @fmdate = DATEADD(day, 1, @fmdate)

end

set @mysql = 'select x_gro_id,x_wk_id,' + @mysql + ' from xy_wk_wage group by x_gro_id,x_wk_id '

exec (@mysql)
go

改成之样这后

第 1 行: '13' 附近有语法错误。

OracleRoob 2006-01-04
  • 打赏
  • 举报
回复
set @mysql = 'sum(case x_wk_pdate when '+@fmdate+' then x_wk_time else 0 end) as '+@fmdate+' ,' + isnull(@mysql,'')

------------------------------------------

@fmdate 是日期型的,必须强制转换为字符串,才能连接

cast(@fmdate as varchar(100))



convert(varchar(100), @fmdate, 120)
WangZWang 2006-01-04
  • 打赏
  • 举报
回复
set @mysql = 'sum(case x_wk_pdate when '+@fmdate+' then x_wk_time else 0 end) as '+@fmdate+' ,' + isnull(@mysql,'')
------------------------这句改为
set @mysql = 'sum(case x_wk_pdate when '''+cast(@fmdate as varchar(30))+''''
then x_wk_time else 0 end) as '''+cast(@fmdate as varchar(30))+'''' ,' + isnull(@mysql,'')
子陌红尘 2006-01-04
  • 打赏
  • 举报
回复
declare @fmdate datetime,@todate datetime,@mysql varchar(1000)
select @fmdate = '2005-11-01'
select @todate = '2005-11-14'

while @fmdate < @todate
begin
set @mysql = 'sum(case x_wk_pdate when '''+convert(char(10),@fmdate,120)+''' then x_wk_time else 0 end) as ['+convert(char(10),@fmdate,120)+'],' + isnull(@mysql,'')
set @fmdate = DATEADD(day, 1, @fmdate)
end

set @mysql = 'select x_gro_id,x_wk_id,' + @mysql + ' from xy_wk_wage group by x_gro_id,x_wk_id '
exec @mysql
go

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧