Can this be written in a stored procedure? Why does it always go wrong?

zhangb

declare @sqlcommand char (200)

select @ sqlcommand = 'select' + rtrim (@TableName) + '. AssessID,' + rtrim (@TableName) + '. RealPoint,' + rtrim (@TableName) + '. assessAccord,' + rtrim (@TableName) + '.ItemComment, AssessItem.ItemName, manager.ManagerName, TotalAssess.CheckComment, TotalAssess.StartTime, TotalAssess.EndTime, TotalAssess.NowTime
        from manager, TotalAssess, AssessItem, '+ rtrim (@TableName) +' where TotalAssess.TotalID = '+ rtrim (@TotalID) +' and TotalAssess.TotalID = Assess.TotalID and Assess.ManagerID = '+ rtrim (@ManagerID) + 'and Assess.ManagerID = manager.ManagerID and Assess.ItemID = AssessItem.ItemID'

  EXEC (@sqlcommand)

.net error message:
The column prefix 'xlglyike' does not match the table name or alias used in the query.
The column prefix 'xlglyike' does not match the table name or alias used in the query.
The column prefix 'xlglyike' does not match the table name or alias used in the query.
The column prefix 'xlglyike' does not match the table name or alias used in the query.
The column prefix 'AssessItem' does not match the table name or alias used in the query.
The column prefix 'manager' does not match the table name or alias used in the query.
The column prefix 'TotalAssess' does not match the table name or alias used in the query.
The column prefix 'TotalAssess' does not match the table name or alias used in the query.
The column prefix 'TotalAssess' does not match the table name or alias used in the query.

'xlglyike' is the dynamic table name

恶魔右指

What is @TableName

zhangb

ALTER PROCEDURE Pr_GetMorePoint
(
    @TotalID int,
    @ManagerID int,
    @TableName varchar (64)
)
AS
This is the @TableName above is the dynamic table name

cv4bf5d65fd

declare @sqlcommand char (200)
Change this sentence to
declare @sqlcommand varchar (8000)

恶魔右指

The variable is not processed well
declare @sqlcommand varchar (200), @tablename varchar (100), @TotalID int, @ManagerID int
set @ tablename = 'ta'
select @ sqlcommand = 'select' + rtrim (@TableName) + '. AssessID,' + rtrim (@TableName) + '. RealPoint,' + rtrim (@TableName) + '. assessAccord,' + rtrim (@TableName) + '.ItemComment, AssessItem.ItemName, manager.ManagerName, TotalAssess.CheckComment, TotalAssess.StartTime, TotalAssess.EndTime, TotalAssess.NowTime
        from manager, TotalAssess, AssessItem, '+ rtrim (@TableName) +' where TotalAssess.TotalID = '+ rtrim (@TotalID) +' and TotalAssess.TotalID = Assess.TotalID and Assess.ManagerID = '+ rtrim (@ManagerID) + 'and Assess.ManagerID = manager.ManagerID and Assess.ItemID = AssessItem.ItemID'

  EXEC (@sqlcommand) This will cause an error

cv4bf5d65fd

declare @sqlcommand varchar (8000)

select @ sqlcommand = 'select' + rtrim (@TableName) + '. AssessID,' + rtrim (@TableName) + '. RealPoint,' + rtrim (@TableName) + '. assessAccord,' + rtrim (@TableName) + '.ItemComment, AssessItem.ItemName, manager.ManagerName, TotalAssess.CheckComment, TotalAssess.StartTime, TotalAssess.EndTime, TotalAssess.NowTime from manager, TotalAssess, AssessItem,' + rtrim (@TableName) + 'where TotalAssess.TotalID =' + str (@TotalID) + 'and TotalAssess.TotalID = Assess.TotalID and Assess.ManagerID =' + str (@ManagerID) + 'and Assess.ManagerID = manager.ManagerID and Assess.ItemID = AssessItem.ItemID'

EXEC (@sqlcommand)

cv4bf5d65fd

@TotalID int,
@ManagerID int,

These two variables are integers. You need to use str () function to convert them into strings to concatenate. Also, int types cannot use rtrim ~~~

wgfxman

declare @sqlcommand char (200)
The first question should be here

The variable is too short, just before the table name