sql - 存储过程插入错误 : Column name or number of supplied values does not match table definition

Question

这是我遇到的唯一错误。请帮忙。提前谢谢你。

在我成功创建 SQL Server 中的存储过程之后。我尝试使用以下(仅示例)执行它

exec [dbo].ExportResourceTime 'ABC111', '', 3, 1, '2013-10-01', '2013-10-30', 1, 21  

然后我得到以下错误。见附件照片。

这是 SSMS 中的实际错误。第 96 行是 if @OrgUnit <> ''

顶部的灰色光标

这是我认为错误的独立脚本。如果我错了，请纠正我。

Msg 213, Level 16, State 1, Procedure ExportResourceTime, Line 96
Insert Error: Column name or number of supplied values does not match table definition.

代码:

create procedure [dbo].ExportResourceTime
        @ResourceID nvarchar(30),
            @OrgUnit nvarchar(15),
            @TimeDetail int,
            @ExpenseDetail int,
            @FromDate Datetime,
            @ToDate Datetime,
            @IncludeID int,
            @TimeTypeGroup int
            --1 = No Time Type Group
            --2 = Group by Time Type
as
BEGIN 
   create table #ItemisedTimeandMaterials
   (
      IDNo int,
      OrderBy1 varchar(60),
      ItemDate datetime,--MOD005
      RevenueTypeCode varchar(24),
      TimeType varchar(24),
      ProjectCode varchar(20),
      taskUID int,
      OutlineNum varchar(60),
      taskname varchar(60),
      activitycode varchar(24),
      ActivityDesc varchar(60),
      ResourceID nvarchar(24),
      OrganizationID nvarchar(15),
      EffectiveDate datetime,
      firstname varchar(60),
      lastname varchar(60),
      ExpenseTypeCode varchar(24),
      ExpenseTypeDesc varchar(60),
      Hours decimal(8,2),
      Rate decimal(8,2),
      Total decimal(20,8),
      Descr varchar(256), --MOD005 DM Added col for relevant detail for Expenses
      TimeTypeCode nvarchar(10)
     )
--GW: move this bit to the top--DONE

create table #Resources
(
ResourceID nvarchar(30),
OrganizationID nvarchar(15),
EffectiveDate datetime
)


if @ResourceID <> ''
 begin
 insert into #Resources (ResourceID,OrganizationID,EffectiveDate)

    select ro.ResourceID, ro.OrganizationID, ro.EffectiveDate from ResourceOrganization ro,
    (select ResourceID, MAX(EffectiveDate) as maxEffectivedate from dbo.ResourceOrganization
    **where ResourceID = @ResourceID**
    group by ResourceID) as maxresults
    where ro.ResourceID = maxresults.ResourceID
    and ro.EffectiveDate = maxresults.maxEffectivedate
    end

if @OrgUnit <> ''
 begin
 insert into #Resources (ResourceID,OrganizationID,EffectiveDate)
  Select  ResourceID,OrganizationID,EffectiveDate from ResourceOrganization
  where OrganizationID like '' + @OrgUnit + '%'
   end

-- get actual time - REGULAR
insert into #ItemisedTimeandMaterials
select      
Case when @IncludeID = 1 then b.timeID else '' end, --mod 07
e.lastname + e.firstname, 
case when @TimeDetail = 2 then g.enddate else (case when @TimeDetail = 3 then b.TimeEntryDate else null end) end,--MOD005
'FEES',
'Regular',
b.projectcode,
b.taskuid,
f.outlinenum,
f.taskname,
b.ActivityCode,
c.ActivityDesc,
b.resourceID,
RES.OrganizationID,
e.firstname,
e.lastname,
'','', -- expense
sum(isnull(b.StandardHours,0)), -- MOD003 - added in isnull's
0,--h.StandardAmt,--b.NegotiatedChargeRate, --MOD005 Change to NegotiatedChargeRate from StandardChargeRate
0,--sum(isnull(b.StandardHours,0)* IsNull(h.standardAmt,0)),--sum(bd.BilledAmt),--MOD005 Change from BillableAmt feild (was incorrect for adjustments)
case when @TimeDetail = 3 then b.invoicecomment else '' end,--MOD005
case when @TimeTypeGroup = 2 then b.TimeTypeCode else '' end--MOD008

from time b 
join activity c
on b.activitycode = c.activitycode
join resource e 
on b.resourceID = e.resourceID
join project p 
on b.ProjectCode=p.ProjectCode 
and p.RevisionStatusCode='A'
join task f 
on b.projectcode = f.projectcode 
and b.taskuid =f.taskuid 
and f.revisionnum = p.RevisionNum
join SCWeekEnding g   
on b.TimeEntryDate between g.StartDate and g.EndDate
join #Resources RES
on b.ResourceID = RES.ResourceID
--left join ratesetresource h on h.resourceid = b.resourceid
where       --b.projectcode = @PROJECTCODE and
b.statuscode in ('A','V','T')
and b.TimeEntryDate >= @FromDate
and b.TimeEntryDate <= @ToDate
and Isnull(b.StandardHours,0) <> 0
and b.resourceid in(Select ResourceId from #Resources)


group by  
b.projectcode,
b.taskuid,
f.outlinenum,
f.taskname,
b.ActivityCode,
c.ActivityDesc,
b.resourceID,
RES.OrganizationID,
e.firstname,
e.lastname,

case when @TimeDetail = 2 then g.enddate else (case when @TimeDetail = 3 then b.TimeEntryDate else null end) end,--MOD005
case when @TimeDetail = 3 then b.invoicecomment else '' end,
Case when @IncludeID = 1 then b.timeID else '' end, --mod 07
case when @TimeTypeGroup = 2 then b.TimeTypeCode else '' end--MOD008
having sum(isnull(b.StandardHours,0)) <> 0 


-- get actual time - OVERTIME
insert into #ItemisedTimeandMaterials
select      
Case when @IncludeID = 1 then b.timeID else '' end, --mod 07
e.lastname + e.firstname, 
case when @TimeDetail = 2 then g.enddate else (case when @TimeDetail = 3 then b.TimeEntryDate else null end) end,--MOD005
'FEES',
'Overtime',
--GW: need projectcode here--DONE
b.projectcode,
b.taskuid,
f.outlinenum,
f.taskname,
b.ActivityCode,
c.ActivityDesc,
b.resourceID,
RES.OrganizationID as OrgUnit,
e.firstname,
e.lastname,
'','', -- expense
sum(isnull(b.OvertimeHours,0)), -- MOD003 - added in isnull's
0,
0,
case when @TimeDetail = 3 then b.invoicecomment else '' end, --MOD005
case when @TimeTypeGroup = 2 then b.TimeTypeCode else '' end--MOD008
from time b 
join activity c 
on b.activitycode = c.activitycode
join resource e 
on b.resourceID = e.resourceID
join project p 
on b.ProjectCode=p.ProjectCode 
and p.RevisionStatusCode='A'
join task f
on b.projectcode = f.projectcode 
and b.taskuid =f.taskuid 
and f.revisionnum = p.RevisionNum
join SCWeekEnding g   
on b.TimeEntryDate between g.StartDate and g.EndDate
join #Resources RES
on b.ResourceID = RES.ResourceID

where       
b.statuscode in ('A','V','T')
--and f.revisionnum = @latestapprovedrevision
and b.TimeEntryDate >= @FromDate
and b.TimeEntryDate <= @ToDate
and Isnull(b.OvertimeHours,0) <> 0
and b.resourceid in (select resourceid from #Resources)

group by  
--GW: projectcode here--DONE
b.projectcode,
b.taskuid,
f.outlinenum,
f.taskname,
b.ActivityCode,
c.ActivityDesc,
b.resourceID,
RES.OrganizationID,
e.firstname,
e.lastname,

case when @TimeDetail = 2 then g.enddate else (case when @TimeDetail = 3 then b.TimeEntryDate else null end) end,--MOD005
case when @TimeDetail = 3 then b.invoicecomment else '' end,
case when @IncludeID = 1 then b.TimeID else '' end ,--mod 07
case when @TimeTypeGroup = 2 then b.TimeTypeCode else '' end--MOD008
having sum(isnull(b.Overtimehours,0)) <> 0

/**************** SECTION 7: OUTPUT DATA TO CRYSTAL REPORT ******************/

-- return data to the Crystal report only if taskuid are in table 1

select * 
from #ItemisedTimeandMaterials
order by taskUID

drop table #Resources
drop table #ItemisedTimeandMaterials

END

GO

Answer 1

#ItemisedTimeandMaterials 临时表中有 23 列，而您只从选择列表中插入 22 个值。

当从 SELECT 列表插入具有大量计算值的列的表时，您可以通过为每个计算列提供与目标临时表列相同的别名来跟踪列。

通过这种方式，您可以轻松地比较表列和选择列表中的列，并找出缺失的列。

缺少的列是 EffectiveDate ，将它添加到正确位置的选择列表中

Select
..
..
b.resourceID,
RES.OrganizationID,
RES.EffectiveDate, --- Missing column
e.firstname,
e.lastname, 
...
..