Dynamic SQL error during stored procedure

zhangb

.net environment prompt information:
"Line 1: Syntax error near '='."
The stored procedure is:


ALTER PROCEDURE Pr_UpdatePoint
(
@ManagerID int,
@departID int,
@StartTime datetime,
@EndTime datetime,
@NowTime datetime,
@TableName varchar (64)
)
AS
Ranch
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 = "Unaudited" where TotalID =' + @ tempID + '')

end
 
Don't know what's wrong

cv4bf5d65fd

ALTER PROCEDURE Pr_UpdatePoint
(
@ManagerID int,
@departID int,
@StartTime datetime,
@EndTime datetime,
@NowTime datetime,
@TableName varchar (64)
)
AS
Ranch
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 = `` Unaudited' 'where TotalID =' + @ tempID + '')

end

Look at it like this

cv4bf5d65fd

TotalID = null you are wrong
Should be TotalID is null

michealhenry

Print the @sqlcommand variable
See what happens?

zhangb

cv4bf5d65fd
---------------------------------------
I changed it, no, the two writing methods should be the same

michealhenry
----------------------------------------
I print the same result?

zhangb

Let us help you to see if it is a syntax error or a program error?

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 --- add spaces here

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] --- Add spaces here (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 = `` not reviewed' 'where TotalID =' + @ tempID + '')

end

--Look at it like this. You have no spaces in two places

cv4bf5d65fd

exec ('update TotalAssess set ManagerID =' + @ ManagerID + ', departID =' + @ departID + ', checkYN = `` Unaudited' 'where TotalID =' + @ tempID + '')

This sentence is not correct, @ManagerID, @departID, @tempID are all int, can not be connected as a string, use rtrim () to expand,

exec ('update TotalAssess set ManagerID =' + rtrim (@ManagerID) + ', departID =' + rtrim (@departID) + ', checkYN = `` Unaudited' 'where TotalID =' + rtrim (@tempID))

zhangb

cv4bf5d65fd:
Tip: There is a syntax error near '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)

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

exec ('update TotalAssess set ManagerID =' + @ ManagerID + ', departID =' + @ departID + ', checkYN = `` Unaudited' 'where TotalID =' + @ tempID)

end