运行存储过程报这个错误,为什么?
ALTER PROCEDURE [dbo].[Sourcefenxi1](@dDate datetime,@BuilldNo nvarchar(20))
AS
begin
declare @Sql nvarchar(max)
declare @tableName nvarchar(100)
declare @tableName2 nvarchar(100)
declare @sqlwhere nvarchar(100)
--选择最适用的数据表
set @tableName = 'dbo.TMETER' + CAST(datepart(YYYY,@dDate) as varchar(4))
set @tableName2 = 'dbo.TELECTRI' + CAST(datepart(YYYY,@dDate) as varchar(4))
set @sqlwhere=convert(varchar(20), @dDate)
--拼接查询语句
set @Sql = N'
select c.AREAGUID,c.BUILDNO,c.BUILDNAME,累计耗电量,当日耗电,电单耗,累计耗水量,当日耗水量,水单耗,累计耗热,计划供热,单日供热量,热量单耗,抄表时间 from TBUILD c,(
select f.AREAGUID ,f.BUILDNO,累计耗电量,当日耗电,电单耗,累计耗水量,当日耗水量,水单耗,f.METERNLRL as 累计耗热 ,f.METERJHGR as 计划供热,f.METERDAN as 单日供热量,f.METERRLDH as 热量单耗, d.抄表时间 from '+@tableName+' f,
(select a.AREAGUID,a.buildno,a. METERNLLJ as 累计耗电量,a.METERDAY as 当日耗电 ,a.METERDAN as 电单耗,b.METERNLLJ as 累计耗水量,b.METERDAY as 当日耗水量,b.METERDAN as 水单耗,b.DDATE as 抄表时间
from '+@tableName2+' a inner join (select e.BUILDNO,e.AREAGUID,e.DDATE,e.DEVICETYPE, e.METERNLLJ,e.METERDAY,e.METERDAN from '+@tableName2+' e where e.DEVICETYPE=11) b
on a.DDate=b.DDate and a.AREAGUID=b.AREAGUID and a.BUILDNO=b.BUILDNO and a.DEVICETYPE!=b.DEVICETYPE
and convert(varchar(20),a.DDate,23)='+@sqlwhere+' and a.BUILDNO='+@BuilldNo+'
)d
where f.AREAGUID=d.AREAGUID and f.BUILDNO=d.BUILDNO and f.DDATE=d.抄表时间 and CONVERT(varchar(20),f.DDate,112)='+@sqlwhere+' and f.BUILDNO='+@BuilldNo+') g
where c.AREAGUID=g.AREAGUID and c.BUILDNO=g.BUILDNO
'
exec(@Sql)
end;
存储过程输入2个参数@dDate=2013-10-16 ,@BuilldNo=132
报错误
消息 102,级别 15,状态 1,第 7 行
'16' 附近有语法错误。
AS
begin
declare @Sql nvarchar(max)
declare @tableName nvarchar(100)
declare @tableName2 nvarchar(100)
declare @sqlwhere nvarchar(100)
--选择最适用的数据表
set @tableName = 'dbo.TMETER' + CAST(datepart(YYYY,@dDate) as varchar(4))
set @tableName2 = 'dbo.TELECTRI' + CAST(datepart(YYYY,@dDate) as varchar(4))
set @sqlwhere=convert(varchar(20), @dDate)
--拼接查询语句
set @Sql = N'
select c.AREAGUID,c.BUILDNO,c.BUILDNAME,累计耗电量,当日耗电,电单耗,累计耗水量,当日耗水量,水单耗,累计耗热,计划供热,单日供热量,热量单耗,抄表时间 from TBUILD c,(
select f.AREAGUID ,f.BUILDNO,累计耗电量,当日耗电,电单耗,累计耗水量,当日耗水量,水单耗,f.METERNLRL as 累计耗热 ,f.METERJHGR as 计划供热,f.METERDAN as 单日供热量,f.METERRLDH as 热量单耗, d.抄表时间 from '+@tableName+' f,
(select a.AREAGUID,a.buildno,a. METERNLLJ as 累计耗电量,a.METERDAY as 当日耗电 ,a.METERDAN as 电单耗,b.METERNLLJ as 累计耗水量,b.METERDAY as 当日耗水量,b.METERDAN as 水单耗,b.DDATE as 抄表时间
from '+@tableName2+' a inner join (select e.BUILDNO,e.AREAGUID,e.DDATE,e.DEVICETYPE, e.METERNLLJ,e.METERDAY,e.METERDAN from '+@tableName2+' e where e.DEVICETYPE=11) b
on a.DDate=b.DDate and a.AREAGUID=b.AREAGUID and a.BUILDNO=b.BUILDNO and a.DEVICETYPE!=b.DEVICETYPE
and convert(varchar(20),a.DDate,23)='+@sqlwhere+' and a.BUILDNO='+@BuilldNo+'
)d
where f.AREAGUID=d.AREAGUID and f.BUILDNO=d.BUILDNO and f.DDATE=d.抄表时间 and CONVERT(varchar(20),f.DDate,112)='+@sqlwhere+' and f.BUILDNO='+@BuilldNo+') g
where c.AREAGUID=g.AREAGUID and c.BUILDNO=g.BUILDNO
'
exec(@Sql)
end;
存储过程输入2个参数@dDate=2013-10-16 ,@BuilldNo=132
报错误
消息 102,级别 15,状态 1,第 7 行
'16' 附近有语法错误。
...全文
相关推荐
K3用administrator用户登录报运行时错误2007
K3用administrator用户登录报运行时错误2007
Oracle报存储过程中调用DBLink同义词出现错误:PLS-00201: 必须声明标识符
主要介绍了Oracle报存储过程中调用DBLink同义词出现错误:PLS-00201: 必须声明标识符的相关资料,需要的朋友可以参考下
IDEA Debug启动tomcat报60659端口占用错误的解决
工作中将开发工具由Eclipse转为IntelliJ IDEA,在使用过程中遇到许多问题,其中60659端口占用错误对于不熟悉IDEA的开发者来说或许会比较头痛,本文就来解决一下这个问题
解决Intellij IDEA运行报Command line is too long的问题
主要介绍了解决Intellij IDEA运行报Command line is too long的问题,本文通过两种方案给大家详细介绍,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下
oracle存储过程编译错误问题
NULL 博文链接:https://forlab.iteye.com/blog/1821751


应用实例
