CODEWITHSUNDEEP
sqlsql-server-2005
HEEN
HEEN

Reputation: 4721

Stored procedure giving error while adding two more columns

I have an SP, which was working fine until I added two more columns. Now after adding two more columns it started giving error as

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.

But I have already inserted that columns.

two newly added columns are

Dept_received varchar(110), doc_type varchar(110)

Below is my SP.

ALTER PROCEDURE [dbo].[Inward_Rec_Dept_doc]      
        @From_date Datetime,      
        @To_date Datetime      

        AS      
        BEGIN      
        CREATE TABLE #temp(      
        Dept_received varchar(110), doc_type varchar(110), date datetime, Total int,doc_From_To varchar(50),Inward int, First_Level_Transfer int,      
        Data_Entry_Transfer int,       
        Second_Level_Transfer int, Outward_Transfer int,      
        Closed int, Communication_Transfer int, Returned int     
    )      

      INSERT INTO #temp      
      (Dept_received, doc_type, date, Total,doc_From_To, Inward, First_Level_Transfer,      
       Data_Entry_Transfer,       
       Second_Level_Transfer, Outward_Transfer,      
       Closed, Communication_Transfer, Returned)      
      SELECT      
       doc_date, COUNT(*),      
     (select kk.doc_no FROM inward_doc_tracking_hdr kk where mkey in (select min(mkey) FROM inward_doc_tracking_hdr jj  
     where jj.doc_date =convert(datetime,aa.doc_date,103) )  )  
       + ' - '+  
     (select kk.doc_no FROM inward_doc_tracking_hdr kk where mkey in (select max(mkey) FROM inward_doc_tracking_hdr jj  
     where jj.doc_date =convert(datetime,aa.doc_date,103) )  )  
       ,SUM(      
       CASE      
        WHEN status_flag in ('6','23') THEN 1 ELSE 0      
       END)  
       ,SUM(      
       CASE      
          WHEN status_flag in ('4','26','24') THEN 1 ELSE 0      
       END)  
       ,SUM(      
       CASE      
          WHEN status_flag in ('15','20') THEN 1 ELSE 0      
       END),      
       SUM(      
       CASE      
          WHEN status_flag in ('17','21') THEN 1 ELSE 0      
       END),      
       SUM(      
       CASE      
          WHEN status_flag in ('18','27') THEN 1 ELSE 0      
       END),      
       SUM(      
       CASE      
          WHEN status_flag='5' THEN 1 ELSE 0      
       END),      
       SUM(      
       CASE      
          WHEN status_flag='16' THEN 1 ELSE 0      
       END),    
       SUM(      
       CASE      
          WHEN status_flag='14' THEN 1 ELSE 0      
       END)    
      FROM inward_doc_tracking_hdr  aa    
      WHERE doc_date between @From_date and @To_date   
    --AND status_flag <> '6'      
      GROUP BY doc_date, Dept_received, doc_type    
END      

    Select * from #temp

I am using sql-server-2005

kindly help me what is the error

updated query

ALTER PROCEDURE [dbo].[Inward_Rec_Dept_doc]      
            @From_date Datetime,      
            @To_date Datetime      

            AS      
            BEGIN      
            CREATE TABLE #temp( 
            Dept_received datetime, 
            doc_type varchar(110), date datetime, Total int,doc_From_To varchar(50), 
            Inward int, First_Level_Transfer int, 
            Data_Entry_Transfer int, 
            Second_Level_Transfer int, Outward_Transfer int, 
            Closed int, Communication_Transfer int, Returned int 
       )

          INSERT INTO #temp      
          (
                Dept_received, 
                doc_type, 
                date, 
                Total,
                doc_From_To, 
                Inward, 
                First_Level_Transfer,      
                Data_Entry_Transfer,       
                Second_Level_Transfer, 
                Outward_Transfer,      
                Closed, 
                Communication_Transfer, 
                Returned
               )      
          SELECT      
           doc_date, COUNT(*),      
         (select kk.doc_no FROM inward_doc_tracking_hdr kk where mkey in (select min(mkey) FROM inward_doc_tracking_hdr jj  
         where jj.doc_date =convert(datetime,aa.doc_date,103) )  )  
           + ' - '+  
         (select kk.doc_no FROM inward_doc_tracking_hdr kk where mkey in (select max(mkey) FROM inward_doc_tracking_hdr jj  
         where jj.doc_date =convert(datetime,aa.doc_date,103) )  )  
           ,SUM(      
           CASE      
            WHEN status_flag in ('6','23') THEN 1 ELSE 0      
           END)  
           ,SUM(      
           CASE      
              WHEN status_flag in ('4','26','24') THEN 1 ELSE 0      
           END)  
           ,SUM(      
           CASE      
              WHEN status_flag in ('15','20') THEN 1 ELSE 0      
           END),      
           SUM(      
           CASE      
              WHEN status_flag in ('17','21') THEN 1 ELSE 0      
           END),      
           SUM(      
           CASE      
              WHEN status_flag in ('18','27') THEN 1 ELSE 0      
           END),      
           SUM(      
           CASE      
              WHEN status_flag='5' THEN 1 ELSE 0      
           END),      
           SUM(      
           CASE      
              WHEN status_flag='16' THEN 1 ELSE 0      
           END),    
           SUM(      
           CASE      
              WHEN status_flag='14' THEN 1 ELSE 0      
           END),1,1  
          FROM inward_doc_tracking_hdr  aa    
          WHERE doc_date between @From_date and @To_date   
        --AND status_flag <> '6'      
          GROUP BY doc_date, dept_received, doc_type 
  END      

  Select * from #temp

Upvotes: 1

Views: 62

Answers (2)

Y.B.
Y.B.

Reputation: 3586

You can save trouble aligning INSERTed columns count and types with the temporary table structure by using SELECT INTO:

ALTER PROCEDURE [dbo].[Inward_Rec_Dept_doc]      
  @From_date Datetime,      
  @To_date Datetime      
AS      
BEGIN      
  IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;

  SELECT
    doc_date As [date], dept_received, doc_type, COUNT(*) As Total
   ,(SELECT kk.doc_no FROM inward_doc_tracking_hdr kk WHERE mkey in (SELECT min(mkey) FROM inward_doc_tracking_hdr jj WHERE jj.doc_date = convert(datetime,aa.doc_date,103) ) )
    + ' - '+
    (SELECT kk.doc_no FROM inward_doc_tracking_hdr kk WHERE mkey in (SELECT max(mkey) FROM inward_doc_tracking_hdr jj WHERE jj.doc_date = convert(datetime,aa.doc_date,103) ) )
    As doc_From_To
   ,SUM(CASE WHEN status_flag in ( '6', '23')       THEN 1 ELSE 0 END) As Inward
   ,SUM(CASE WHEN status_flag in ( '4', '26', '24') THEN 1 ELSE 0 END) As First_Level_Transfer
   ,SUM(CASE WHEN status_flag in ('15', '20')       THEN 1 ELSE 0 END) As Data_Entry_Transfer
   ,SUM(CASE WHEN status_flag in ('17', '21')       THEN 1 ELSE 0 END) As Second_Level_Transfer
   ,SUM(CASE WHEN status_flag in ('18', '27')       THEN 1 ELSE 0 END) As Outward_Transfer
   ,SUM(CASE WHEN status_flag =    '5'              THEN 1 ELSE 0 END) As Closed
   ,SUM(CASE WHEN status_flag =   '16'              THEN 1 ELSE 0 END) As Communication_Transfer
   ,SUM(CASE WHEN status_flag =   '14'              THEN 1 ELSE 0 END) As Returned
  INTO #temp
  FROM inward_doc_tracking_hdr  aa
  WHERE doc_date between @From_date and @To_date
--AND status_flag <> '6'
  GROUP BY doc_date, dept_received, doc_type

END      
GO

Select * from #temp;

Upvotes: 0

Shilpa Soni
Shilpa Soni

Reputation: 2142

ALTER PROCEDURE [dbo].[Inward_Rec_Dept_doc]      
    @From_date Datetime,      
    @To_date Datetime      

    AS      
    BEGIN      
    CREATE TABLE #temp(      
    Dept_received varchar(110),
     doc_type varchar(110), date datetime, Total int,doc_From_To varchar(50),
    Inward int, First_Level_Transfer int,      
    Data_Entry_Transfer int,       
    Second_Level_Transfer int, Outward_Transfer int,      
    Closed int, Communication_Transfer int, Returned int     
)      

  INSERT INTO #temp      
  (Dept_received, doc_type, date, Total,doc_From_To, Inward, First_Level_Transfer,      
   Data_Entry_Transfer,       
   Second_Level_Transfer, Outward_Transfer,      
   Closed, Communication_Transfer, Returned)      
  SELECT      
   doc_date, COUNT(*),      
 (select kk.doc_no FROM inward_doc_tracking_hdr kk where mkey in (select min(mkey) FROM inward_doc_tracking_hdr jj  
 where jj.doc_date =convert(datetime,aa.doc_date,103) )  )  
   + ' - '+  
 (select kk.doc_no FROM inward_doc_tracking_hdr kk where mkey in (select max(mkey) FROM inward_doc_tracking_hdr jj  
 where jj.doc_date =convert(datetime,aa.doc_date,103) )  )  
   ,SUM(      
   CASE      
    WHEN status_flag in ('6','23') THEN 1 ELSE 0      
   END)  
   ,SUM(      
   CASE      
      WHEN status_flag in ('4','26','24') THEN 1 ELSE 0      
   END)  
   ,SUM(      
   CASE      
      WHEN status_flag in ('15','20') THEN 1 ELSE 0      
   END),      
   SUM(      
   CASE      
      WHEN status_flag in ('17','21') THEN 1 ELSE 0      
   END),      
   SUM(      
   CASE      
      WHEN status_flag in ('18','27') THEN 1 ELSE 0      
   END),      
   SUM(      
   CASE      
      WHEN status_flag='5' THEN 1 ELSE 0      
   END),      
   SUM(      
   CASE      
      WHEN status_flag='16' THEN 1 ELSE 0      
   END),    
   SUM(      
   CASE      
      WHEN status_flag='14' THEN 1 ELSE 0      
   END),1,1  --Add your values in place of 1, your issue will be resolved    
  FROM inward_doc_tracking_hdr  aa    
  WHERE doc_date between @From_date and @To_date   
--AND status_flag <> '6'      
  GROUP BY doc_date, Dept_received, doc_type    
END      

Select * from #temp

Upvotes: 1

Related Questions