Geek Answers Handbook

The stored procedure throws an error in SQL Server 2012, but works fine in SQL Server 2000

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 database.

[SQLServer JDBC Driver] [SQLServer] The select list for the INSERT statement contains fewer elements than the insert list. The number of SELECT values ​​must match the number of INSERT columns.

Can anyone help me sort 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
-1
source share
1 answer
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

you are trying to insert 3 columns in #tmp, but the query returns only 2 columns

, Count (*) ()

+1

More articles:


All Articles