Column name or number of supplied values does not match table definition.


  • Hi,

    In one of my SP i am trying to insert records in temp table using another SP using below script.

     INSERT INTO #ResultatPerson  
      ,PAPER_OfficielltFirmanamn --B920LI 080911  
      ,PAPER_EnskildFirma --B920LI 080911  
      ,PAPER_GSI_FirmaKalla_Kod --B920LI 080911  
      ,PAPER_GSI_RiskKlass_Kod --B920LI 081218  
      ,PAPER_PEP --B920LI 081218  
                    ,PAR_ID_GallandeHushall --B90AJS 090115  
     EXEC DEPA_Person_SP   
        ,@Resultatkod OUTPUT  
        ,@Resultattext OUTPUT  
        ,@PAR_ID_Resultat OUTPUT  

    Now suppose SP DEPA_Person_SP returen more columns then select statement then in this case does this code work.

    please suggest.

    Wednesday, November 27, 2013 10:29 AM


All replies

  • Yes. you need to make sure table exactly matches the stored procedure resultset

    If you cant change the table to match resultset or change SP to return only columns included in table, then only way is to use distributed query methods like OPENROWSET

    see example 2 here

    Please Mark This As Answer if it helps to solve the issue Visakh ----------------------------

    Wednesday, November 27, 2013 10:34 AM
  • More columns than INSERT has, that what  you meant? In this case number of columns should matched.

    Best Regards,Uri Dimant SQL Server MVP,

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, November 27, 2013 11:41 AM
  • A quick fix (maybe not ideal) would be :- Insert the output of the stored proc into another temp table and then insert into your temp table.
    Wednesday, November 27, 2013 11:48 AM
  • If the columns numbers are different from the resultset of proc and the insert statment column list, then it will not work. 

    Try the below:

       @managerID varchar(20) OUTPUT
    	Select userid,username From [user] WHERE userid = '123123123'
       SELECT @managerID = userid
       From [user] WHERE userid = '123123123'
    Create table #Temp(userid varchar(20),username Varchar(100))
    Declare @mn Varchar(10)
    --if the number of columns are not matching with the resultset of proc,
    --it will break the code.
    Insert into #Temp (userid,username) 
    Exec Getproc @managerID = @mn OUTPUT
    --Select @mn
    Select * From #Temp

    Wednesday, November 27, 2013 12:29 PM
  • You cannot overcome the problem for a number of reasons - foremost is that the use a stored procedure as a sort of virtual table is not really a good design choice, especially if the stored procedure has output arguments.  Given the presence of output arguments, I suggest you either re-implement the stored procedure (and by doing so give it an understandable and useful name) to serve as a source of data by changing it to a TVF, repartition the logic (since I'm guessing that the procedure has some sort of update logic while also providing information back to the caller of the procedure), duplicate whatever logic is needed in a form that you can use within your query/logic, or reconsider the application design that lead to the creation of this procedure.
    Wednesday, November 27, 2013 2:00 PM