SQL语句的条件问题

lsp69 2009-04-24 06:28:23

有以下语句,我先用变量 @Sd 构造了where部分的条件,然后要连接上 and dbo.DateConvertWeek(si) between '+ @Week1 + ' and '+ @Week2
构成条件,但是会报错,说"转换为整形出错",但是我 的@Week1 和@Week2都定义为int 了.

DECLARE @S VARCHAR(8000)

SELECT @S='select salesmanname,isnull(so,''Wating for SO release'') as so,podname,services,FD,
BerthCanceltwentysize*0.5+BerthCancelfourtysize+BerthCancelhq+BerthCancelfortyfivesize as BerthCancelF,
twentysize*0.5+fourtysize+hq+fortyfivesize as ApplyF
from v_bk_BListProc ' + @Sd
+ ' and dbo.DateConvertWeek(si) between '+ @Week1 + ' and '+ @Week2



EXEC(@S)

...全文
91 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
linguojin11 2009-04-28
  • 打赏
  • 举报
回复
顶了。。学习过
htl258_Tony 2009-04-28
  • 打赏
  • 举报
回复
SELECT @S='select '''' as bookingnumber,'''' as jobvslnumber,'''' as shipperfuname, 
'''' as coneefuname,'''' as agentfuname,
vessel+''(Total)'' as vessel,'''' as voyage,'''' as por,'''' as polname,
'''' as etd,'''' as stshname,
'''' as comshname,sum(facttwentysize) as facttwentysize
,sum(factfourtysize) as factfourtysize,sum(facthq) as facthq,
sum(factfortyfivesize) as factfortyfivesize,'''' as bkgaddshname
,'''' as salesmanname,'''' as so,'''' as podname,'''' as services,'''' as FD,
sum(BerthCanceltwentysize)*0.5+sum(BerthCancelfourtysize)+sum(BerthCancelhq)+sum(BerthCancelfortyfivesize) as BerthCancelF,
sum(twentysize)*0.5+sum(fourtysize)+sum(hq)+sum(fortyfivesize) as ApplyF
from v_bk_BListProc ' + ltrim(@Sd)
+ ' and dbo.DateConvertWeek(si) between '+ ltrim(@Week1) + ' and '+ ltrim(@Week2)
+ ' group by vessel'
这样呢?
浩方软件HFWMS 2009-04-28
  • 打赏
  • 举报
回复
SELECT @S='select '' as bookingnumber,'' as jobvslnumber,'' as shipperfuname,
'' as coneefuname,'' as agentfuname,
vessel+''(Total)'' as vessel,'' as voyage,'' as por,'' as polname,
'' as etd,'' as stshname,
'' as comshname,sum(facttwentysize) as facttwentysize
,sum(factfourtysize) as factfourtysize,sum(facthq) as facthq,
sum(factfortyfivesize) as factfortyfivesize,'' as bkgaddshname
,'' as salesmanname,'' as so,'' as podname,'' as services,'' as FD,
sum(BerthCanceltwentysize)*0.5+sum(BerthCancelfourtysize)+sum(BerthCancelhq)+sum(BerthCancelfortyfivesize) as BerthCancelF,
sum(twentysize)*0.5+sum(fourtysize)+sum(hq)+sum(fortyfivesize) as ApplyF
from v_bk_BListProc ' + ltrim(@Sd)
+ ' and dbo.DateConvertWeek(si) between '+ ltrim(@Week1) + ' and '+ ltrim(@Week2)
+ ' group by vessel'

提示:

消息 102,级别 15,状态 1,第 3 行
'Total' 附近有语法错误。
消息 105,级别 15,状态 1,第 11 行
字符串 ' and dbo.DateConvertWeek(si) between 16 and 17 group by vessel' 后的引号不完整。

JonasFeng 2009-04-28
  • 打赏
  • 举报
回复
学习了
  • 打赏
  • 举报
回复
DECLARE @S VARCHAR(8000)

SELECT @S='select salesmanname,isnull(so,''Wating for SO release'') as so,podname,services,FD,
BerthCanceltwentysize*0.5+BerthCancelfourtysize+BerthCancelhq+BerthCancelfortyfivesize as BerthCancelF,
twentysize*0.5+fourtysize+hq+fortyfivesize as ApplyF
from v_bk_BListProc ' + @Sd
+ ' and dbo.DateConvertWeek(si) between '+ cast(@Week1 as varchar(10)) + ' and '+ cast(@Week2 as varchar(10))


EXEC(@S)
幸运的意外 2009-04-24
  • 打赏
  • 举报
回复
dbo.DateConvertWeek(si) 返回的是整数吗,如果不是的话就会报类型转换错误了。还有就是最好把函数的返回值类型发出来看看。
zz6345 2009-04-24
  • 打赏
  • 举报
回复
恩恩饿
zz6345 2009-04-24
  • 打赏
  • 举报
回复
看到了
sdhdy 2009-04-24
  • 打赏
  • 举报
回复
用''引起来的SQL,里面的变量都要转化成字符类型的,才不会出问题。
htl258_Tony 2009-04-24
  • 打赏
  • 举报
回复
主要原因是字符型不能跟数字型在一起拼串,把数字型的也转为字符串就对了.ltrim用起来比较简单,也习惯了.当然你也可以用 cast(@week as varchar)
fan_xiaohu 2009-04-24
  • 打赏
  • 举报
回复
就是转换成整形了才出错。

DECLARE @S VARCHAR(8000)

SELECT @S='select salesmanname,isnull(so,''Wating for SO release'') as so,podname,services,FD,
BerthCanceltwentysize*0.5+BerthCancelfourtysize+BerthCancelhq+BerthCancelfortyfivesize as BerthCancelF,
twentysize*0.5+fourtysize+hq+fortyfivesize as ApplyF
from v_bk_BListProc ' + @Sd
+ ' and dbo.DateConvertWeek(si) between '+ CAST(@Week1 AS NVARCHAR(5)) + ' and '+ CAST(@Week2 AS NVARCHAR(5))


它是说 ' and dbo.DateConvertWeek(si) between ' 这个字符串转换成后面的类型-整形 出错
浩方软件HFWMS 2009-04-24
  • 打赏
  • 举报
回复
直接运行这部分语句,能够成功执行:
SELECT @S='select salesmanname,isnull(so,''Wating for SO release'') as so,podname,services,FD,
BerthCanceltwentysize*0.5+BerthCancelfourtysize+BerthCancelhq+BerthCancelfortyfivesize as BerthCancelF,
twentysize*0.5+fourtysize+hq+fortyfivesize as ApplyF
from v_bk_BListProc ' + @Sd

EXEC(@S)

htl258_Tony 2009-04-24
  • 打赏
  • 举报
回复
DECLARE @S VARCHAR(8000) 

SELECT @S='select salesmanname,isnull(so,''Wating for SO release'') as so,podname,services,FD,
BerthCanceltwentysize*0.5+BerthCancelfourtysize+BerthCancelhq+BerthCancelfortyfivesize as BerthCancelF,
twentysize*0.5+fourtysize+hq+fortyfivesize as ApplyF
from v_bk_BListProc ' + ltrim(@Sd)
+ ' and dbo.DateConvertWeek(si) between '+ ltrim(@Week1) + ' and '+ ltrim(@Week2)


EXEC(@S)

多个ltrim,多份正确保障.

22,206

社区成员

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

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