存储过程中动态SQL运行出错

zhangb

.net环境提示信息：
“第 1 行: '=' 附近有语法错误。”
存储过程为：


ALTER  PROCEDURE Pr_UpdatePoint
(
        @ManagerID int,
        @departID int,
        @StartTime datetime,
        @EndTime datetime,
        @NowTime datetime,
        @TableName varchar(64)
)
AS
       
declare @sqlcommand varchar(2000)
declare @sqlcommand2 varchar(2000)
declare @sqlcommand3 varchar(2000)
declare @sqlcommand4 varchar(2000)
declare @tempID int
declare        @TotalID int

select @sqlcommand='select '+rtrim(@TotalID)+'=TotalID from '+@TableName+' where '+rtrim(@TotalID)+' is null'
exec(@sqlcommand)
if @TotalID is null
begin
  select @sqlcommand2='insert into[TotalAssess]
        (TotalPoint,StartTime,EndTime,NowTime)
        SELECT SUM(RealPoint),'''+convert(varchar,@StartTime,120)+''','''+convert(varchar,@EndTime,120)+''','''+convert(varchar,@NowTime,120)+''' FROM '+@TableName+' where TotalID=null '
exec(@sqlcommand2)


select @sqlcommand3='UPDATE Assess set TotalID='+str(@TotalID)+' where TotalID is null'
exec(@sqlcommand3)

exec('update TotalAssess set ManagerID='+@ManagerID+',departID='+@departID+',checkYN="未审核" where TotalID='+@tempID+'')

end

不知道错在哪里

cv4bf5d65fd

ALTER  PROCEDURE Pr_UpdatePoint
(
        @ManagerID int,
        @departID int,
        @StartTime datetime,
        @EndTime datetime,
        @NowTime datetime,
        @TableName varchar(64)
)
AS
       
declare @sqlcommand varchar(2000)
declare @sqlcommand2 varchar(2000)
declare @sqlcommand3 varchar(2000)
declare @sqlcommand4 varchar(2000)
declare @tempID int
declare        @TotalID int

select @sqlcommand='select '+rtrim(@TotalID)+'=TotalID from '+@TableName+' where '+rtrim(@TotalID)+' is null'
exec(@sqlcommand)
if @TotalID is null
begin
          select @sqlcommand2='insert into[TotalAssess](TotalPoint,StartTime,EndTime,NowTime)
                SELECT SUM(RealPoint),'''+convert(varchar,@StartTime,120)+''','''+convert(varchar,@EndTime,120)+''','''+convert(varchar,@NowTime,120)+''' FROM '+@TableName+' where TotalID is null '
          exec(@sqlcommand2)


        select @sqlcommand3='UPDATE Assess set TotalID='+str(@TotalID)+' where TotalID is null'
        exec(@sqlcommand3)

        exec('update TotalAssess set ManagerID='+@ManagerID+',departID='+@departID+',checkYN=''未审核'' where TotalID='+@tempID+'')

end

这样看看

cv4bf5d65fd

TotalID=null   你这个地方错了   
应该是   TotalID is null

michealhenry

把@sqlcommand变量print一下
看看是什么结果？

zhangb

cv4bf5d65fd
---------------------------------------
我改了一下，不行，这两种写法应该是一样的

michealhenry
----------------------------------------
我print结果也是一样的啊？

zhangb

大家再帮忙看看，究竟是语法错误还是程序错误？

cv4bf5d65fd

ALTER  PROCEDURE Pr_UpdatePoint
(
@ManagerID int,
@departID int,
@StartTime datetime,
@EndTime datetime,
@NowTime datetime,
@TableName varchar(64)
)
AS

declare @sqlcommand varchar(2000)
declare @sqlcommand2 varchar(2000)
declare @sqlcommand3 varchar(2000)
declare @sqlcommand4 varchar(2000)
declare @tempID int
declare @TotalID int  ---这里加空格

select @sqlcommand='select '+rtrim(@TotalID)+'=TotalID from '+@TableName+' where '+rtrim(@TotalID)+' is null'
exec(@sqlcommand)
if @TotalID is null
begin
  select @sqlcommand2='insert into [TotalAssess]       ---这里加空格(TotalPoint,StartTime,EndTime,NowTime)
SELECT SUM(RealPoint),'''+convert(varchar,@StartTime,120)+''','''+convert(varchar,@EndTime,120)+''','''+convert(varchar,@NowTime,120)+''' FROM '+@TableName+' where TotalID is null '
  exec(@sqlcommand2)


select @sqlcommand3='UPDATE Assess set TotalID='+str(@TotalID)+' where TotalID is null'
exec(@sqlcommand3)

exec('update TotalAssess set ManagerID='+@ManagerID+',departID='+@departID+',checkYN=''未审核'' where TotalID='+@tempID+'')

end

--这样看看   你有两个地方没有加空格

cv4bf5d65fd

exec('update TotalAssess set ManagerID='+@ManagerID+',departID='+@departID+',checkYN=''未审核'' where TotalID='+@tempID+'')

这句也不对，@ManagerID 、@departID 、 @tempID  都是int ， 不能当字符串连接，用rtrim()扩起来，

exec('update TotalAssess set ManagerID='+rtrim(@ManagerID)+',departID='+rtrim(@departID)+',checkYN=''未审核'' where TotalID='+rtrim(@tempID))

zhangb

cv4bf5d65fd :
提示：'rtrim'附近有语法错误

cv4bf5d65fd

ALTER  PROCEDURE Pr_UpdatePoint
(
@ManagerID int,
@departID int,
@StartTime datetime,
@EndTime datetime,
@NowTime datetime,
@TableName varchar(64)
)
AS

declare @sqlcommand varchar(2000)
declare @sqlcommand2 varchar(2000)
declare @sqlcommand3 varchar(2000)
declare @sqlcommand4 varchar(2000)
declare @tempID int
declare @TotalID int

select @sqlcommand='select '+@TotalID+'=TotalID from '+@TableName+' where '+@TotalID+' is null'
exec(@sqlcommand)
if @TotalID = null
begin
        select @sqlcommand2='insert into [TotalAssess](TotalPoint,StartTime,EndTime,NowTime) SELECT SUM(RealPoint),'''+convert(varchar,@StartTime,120)+''','''+convert(varchar,@EndTime,120)+''','''+convert(varchar,@NowTime,120)+''' FROM '+@TableName+' where TotalID is null '
          exec(@sqlcommand2)

       
        select @sqlcommand3='UPDATE Assess set TotalID='+@TotalID+' where TotalID is null'
        exec(@sqlcommand3)

        exec('update TotalAssess set ManagerID='+@ManagerID+',departID='+@departID+',checkYN=''未审核'' where TotalID='+@tempID)

end