sql - 存储过程插入错误:列名或提供的值数与表定义不匹配

这是我得到的唯一错误。请帮忙。先感谢您。在我成功创建并在SQL Server中存储过程之后。我试图用以下代码执行它(仅示例)exec [dbo].ExportResourceTime 'ABC111', '', 3, 1, '2013-10-01', '2013-10-30', 1, 21然后我得到以下错误。见附图。这是SSMS中的实际错误。第96行是@OrgUnit ''顶部的灰色光标这是我认为错误来自的独立脚本。如果我错了纠正我。 消息213，级别16，状态1，过程ExportResourceTime，第96行 插入错误:列名或提供的值数与表定义不匹配。码: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 TypeasBEGIN 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--DONEcreate 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 endif @OrgUnit <> '' begin insert into #Resources (ResourceID,OrganizationID,EffectiveDate) Select ResourceID,OrganizationID,EffectiveDate from ResourceOrganization where OrganizationID like '' + @OrgUnit + '%' end-- get actual time - REGULARinsert into #ItemisedTimeandMaterialsselectCase when @IncludeID = 1 then b.timeID else '' end, --mod 07e.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,'','', -- expensesum(isnull(b.StandardHours,0)), -- MOD003 - added in isnull's0,--h.StandardAmt,--b.NegotiatedChargeRate, --MOD005 Change to NegotiatedChargeRate from StandardChargeRate0,--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,--MOD005case when @TimeTypeGroup = 2 then b.TimeTypeCode else '' end--MOD008from time bjoin activity con b.activitycode = c.activitycodejoin resource eon b.resourceID = e.resourceIDjoin project pon b.ProjectCode=p.ProjectCodeand p.RevisionStatusCode='A'join task fon b.projectcode = f.projectcodeand b.taskuid =f.taskuidand f.revisionnum = p.RevisionNumjoin SCWeekEnding gon b.TimeEntryDate between g.StartDate and g.EndDatejoin #Resources RESon b.ResourceID = RES.ResourceID--left join ratesetresource h on h.resourceid = b.resourceidwhere --b.projectcode = @PROJECTCODE andb.statuscode in ('A','V','T')and b.TimeEntryDate >= @FromDateand b.TimeEntryDate <= @ToDateand Isnull(b.StandardHours,0) <> 0and b.resourceid in(Select ResourceId from #Resources)group byb.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,--MOD005case when @TimeDetail = 3 then b.invoicecomment else '' end,Case when @IncludeID = 1 then b.timeID else '' end, --mod 07case when @TimeTypeGroup = 2 then b.TimeTypeCode else '' end--MOD008having sum(isnull(b.StandardHours,0)) <> 0-- get actual time - OVERTIMEinsert into #ItemisedTimeandMaterialsselectCase when @IncludeID = 1 then b.timeID else '' end, --mod 07e.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--DONEb.projectcode,b.taskuid,f.outlinenum,f.taskname,b.ActivityCode,c.ActivityDesc,b.resourceID,RES.OrganizationID as OrgUnit,e.firstname,e.lastname,'','', -- expensesum(isnull(b.OvertimeHours,0)), -- MOD003 - added in isnull's0,0,case when @TimeDetail = 3 then b.invoicecomment else '' end, --MOD005case when @TimeTypeGroup = 2 then b.TimeTypeCode else '' end--MOD008from time bjoin activity con b.activitycode = c.activitycodejoin resource eon b.resourceID = e.resourceIDjoin project pon b.ProjectCode=p.ProjectCodeand p.RevisionStatusCode='A'join task fon b.projectcode = f.projectcodeand b.taskuid =f.taskuidand f.revisionnum = p.RevisionNumjoin SCWeekEnding gon b.TimeEntryDate between g.StartDate and g.EndDatejoin #Resources RESon b.ResourceID = RES.ResourceIDwhereb.statuscode in ('A','V','T')--and f.revisionnum = @latestapprovedrevisionand b.TimeEntryDate >= @FromDateand b.TimeEntryDate <= @ToDateand Isnull(b.OvertimeHours,0) <> 0and b.resourceid in (select resourceid from #Resources)group by--GW: projectcode here--DONEb.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,--MOD005case when @TimeDetail = 3 then b.invoicecomment else '' end,case when @IncludeID = 1 then b.TimeID else '' end ,--mod 07case when @TimeTypeGroup = 2 then b.TimeTypeCode else '' end--MOD008having 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 1select *from #ItemisedTimeandMaterialsorder by taskUIDdrop table #Resourcesdrop table #ItemisedTimeandMaterialsENDGO 最佳答案 当您从选择列表中仅插入22个值时，#ItemisedTimeandMaterials临时表中有23列。当从SELECT列表中插入具有大量具有计算值的列的表时，可以通过为每个计算列赋予别名来与目标临时表列相同来跟踪列。这样，您可以轻松比较表列和选择列表中的列，并找出缺失的列。缺少的列是EffectiveDate，将其添加到选择列表的正确位置Select....b.resourceID,RES.OrganizationID,RES.EffectiveDate, --- Missing columne.firstname,e.lastname,.....关于sql - 存储过程插入错误:列名或提供的值数与表定义不匹配，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20918743/