存储过程在 SQL Server 2012 中引发错误,但在 SQL Server 2000 中工作正常 [英] Stored procedure throws error in SQL Server 2012 but works fine in SQL Server 2000
问题描述
我有一个在 SQL Server 2000 中运行良好的存储过程,但在我们将数据库移动到的 SQL Server 2012 Express 上引发错误.
I have a stored procedure that works fine in SQL Server 2000 but throws an error on SQL Server 2012 Express where we are moving the DB to.
[SQLServer JDBC Driver][SQLServer]INSERT 语句的选择列表包含的项目少于插入列表.SELECT 值的数量必须与 INSERT 列的数量相匹配.
[SQLServer JDBC Driver][SQLServer]The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
有人能帮我解决这个问题吗?
Anybody able to help me sorting this out?
CREATE PROCEDURE spSelectTopCourses
AS
declare @divisor int
declare @column int
set @column = 6 -- number of columns in the site
create table #tmpID (
CityID int
)
insert into #tmpID (CityID)
Select top 40 -- total number of cities to be shown
c.RID as CityID
From Course c
Inner join StateRegions Sr on Sr.RID = c.RID
Inner join Client_Round cr on cr.CourseID = c.CourseID
Group by c.RID
Order by Count(cr.OrderID) desc
create table #tmp (
CityID int,
CityName varchar(100),
TotalOrder int identity(1,1)
)
insert into #tmp (CityID, CityName, TotalOrder)
Select distinct
sr.RID as CityID,
sr.Rname as CityName
From
#tmpID tid
Inner join StateRegions Sr on Sr.RID = tid.CityID
Group by Sr.RName, sr.RID
Order by sr.RName
select @divisor = ceiling(count(*) * 1.0 / @column)
from #tmp
create table #tmpC (
Position int
)
declare @i int
set @i = 1
while @i <= @divisor * @column
begin
insert into #tmpC(Position)
select @i
set @i = @i + 1
end
select
t.CityID, t.CityName + ' Golf' CityName, cnt.CountryID, c.Position,
(c.Position - 1) % @divisor WebOrder1,
(c.Position - 1) / @divisor WebOrder2
from
#tmpC c
left join #tmp t on t.TotalOrder = c.Position
inner join StateRegions sr on sr.RID = t.CityID
inner join CountryStates cs on cs.StateID = sr.StateID
inner join Country cnt on cnt.CountryID = cs.CountryID
order by WebOrder1, WebOrder2
drop table #tmpC
drop table #tmp
drop table #tmpID
推荐答案
insert into #tmp (CityID, CityName, TotalOrder)
Select distinct sr.RID as CityID,
sr.Rname as CityName
From #tmpID tid
Inner join StateRegions Sr on Sr.RID = tid.CityID
Group by Sr.RName,
sr.RID
Order by sr.RName
您正在尝试将 3 列插入 #tmp,但查询仅返回 2 列
you are trying to insert 3 columns into #tmp, but the query only returns 2 columns
我怀疑,您想要 Count(*) 或 Sum(Orders) 作为第三列
I suspect, you want Count(*) or Sum(Orders) as the third column
这篇关于存储过程在 SQL Server 2012 中引发错误,但在 SQL Server 2000 中工作正常的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!