必须在sql过程中声明标量变量“@tempattend”吗？

I am getting an error stating that I Must declare the table variable "@tempattend". Please help me. How can I pass @tempattend table to @tempTableSelect variable?

我收到一个错误，指出我必须声明表变量“@tempattend”。请帮帮我。如何将@tempattend表传递给@tempTableSelect变量？

ALTER PROCEDURE [dbo].[Rl_LM_AHS]

 @SupEmpID nvarchar(10),
 @SectorName nvarchar(300),
 @dateList nvarchar(300),
 @Month nvarchar(5),
 @Year nvarchar(5),
 @SearchType nvarchar(10)


AS
BEGIN
    SET NOCOUNT ON;
    declare @tempTableSelect nvarchar(2000)
    DECLARE @tempattend Table
(
    [Emp.ID] nvarchar(10),
    [Name] nvarchar(60),
    [1] nvarchar(3) null,
    [2] nvarchar(3) null,
    [3] nvarchar(3) null
    upto ..............
    [31] nvarchar(3) null
)

IF (@SearchType = 1)
    BEGIN

--INSERT TEAM LIST TO @tempattend TABLE
 insert into @tempattend([Emp.ID],[Name]) (Select EMP.empID as [Emp.ID],CONCAT(EMP.emp_fname,' ',COALESCE(nullif(EMP.emp_Mname,'') +' ',''),COALESCE(nullif(EMP.emp_Lname,'') +' ','')) as [Name] from EShiftHistory)

set @tempTableSelect = 'select [Emp.ID],Name,' + @dateList +' from @tempattend'

    EXEC (@tempTableSelect)

    END


END

2 个解决方案

#1

You should write

你应该写

set @tempTableSelect = 'select [Emp.ID],Name,' + @dateList +' from @tempattend'

@tempattend is a temporary table variable. It holds a table, not some value like @datelist.

@tempattend是一个临时表变量。它拥有一个表，而不是@datelist这样的值。

But why do you EXEC instead of just selecting directly from the table?

但是你为什么要EXEC而不是直接从表中选择？

Come to think of it: It may not be possible to use memory temp tables in EXEC statements. Try turning this

想一想：在EXEC语句中可能无法使用内存临时表。试着转过来

DECLARE @tempattend Table

into

成

CREATE TABLE #tempattend

and change every occurance of @tempattend to #tempattend.

并将@tempattend的每次出现更改为#tempattend。

#2

Following up on Thorsten Dittmar's answer, why not something like this?

跟进Thorsten Dittmar的回答，为什么不这样呢？

select empID,
 CONCAT(emp_fname,' ',
 COALESCE(emp_Mname,''),
 COALESCE(nullif(emp_Lname,''))) as [Name]
    from EShiftHistory

I realize you're trying to do some dynamic selection with the @dateList variable, but that's a strong signal that you should either normalize your tables or just let your client code pick out which columns it wants. (Not everything needs to be done in raw SQL.)

我意识到你正在尝试使用@dateList变量进行一些动态选择，但这是一个强烈的信号，你应该规范化你的表，或者让你的客户端代码选择它想要的列。（并非所有内容都需要在原始SQL中完成。）

Numbers as column names? Definitely problematic.

数字作为列名？绝对有问题。

#1