Column name or number of supplied values does not match table definition in stored procedure

Question

I added a new line like this:

@imageunc + 'signatures\' + RTRIM(salespoc.SLPRSNFN) + ' '
    + RTRIM(salespoc.SPRSNSLN) + '.jpg' AS 'SigImagePath'

Then started receiving an error:

"Msg 213, Level 16, State 1, Procedure MVTS_repContractQuote, Line 58 Column name or number of supplied values does not match table definition."

Below is my stored procedure. Can someone help me on this?

USE [TEST]
GO
/****** Object:  StoredProcedure [dbo].[TEST_repContractQuote]    Script Date: 09/08/2014 10:48:26         ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
exec TEST_repContractQuote '0000000023 '
exec TEST_repContractQuote '1000000045 '
select STRTDATE, ENDDATE, CONFGREF, * from svc00601 where contnbr='1000000045'
*/
ALTER procedure [dbo].[MVTS_repContractQuote] @ContractNo AS VARCHAR(20) AS
BEGIN


DECLARE @rep TABLE (
    CustNo VARCHAR(20),
    CustName VARCHAR(200),
    Addr1 VARCHAR(100),
    Addr2 VARCHAR(100),
    Addr3 VARCHAR(100),
    CSZ VARCHAR(100),
    Country VARCHAR(50),
    ContactPerson VARCHAR(50),
    Phone VARCHAR(20),
    Fax VARCHAR(20),
    Qty NUMERIC(9,2),
    Desc1 VARCHAR(200),
    Desc2 VARCHAR(200),
    StartDate DATETIME,
    EndDate DATETIME,
    Desc3 VARCHAR(200),
    LineNoteindx NUMERIC(19,5),
    LineNotes TEXT,
    LineTotal NUMERIC(12,2),
    Total NUMERIC(12,2),
    CurrSymbol VARCHAR(5),
    TermsOfSale VARCHAR(100),
    PaymentTerms VARCHAR(200),
    Warranty VARCHAR(100),
    QuoteValidFor VARCHAR(100),
    SalesPerson VARCHAR(200),
    ContStatus SMALLINT,
    Noteindx NUMERIC(19,5),
    Notes TEXT)

-- Get the name of a random images in the d:\gpshare\images directory
declare  @cmd        varchar(255),
    @rc         int,
    @imagepath  varchar(1024),
    @imagedir   varchar(255),
    @imageunc   varchar(255)

select @imagedir = 'D:\gpshare\images\'
select @imageunc = '\\dynamics\gpshare\images\'
select @cmd      = 'dir /b/a-d-h ' + @imagedir

declare @output table (output varchar(255) null)
insert @output exec @rc = master..xp_cmdshell @cmd
select @imagepath = (select TOP 1 @imageunc+output from @output where output is not null ORDER BY NEWID())


INSERT @rep
SELECT h.CUSTNMBR, 
    cust.CUSTNAME AS 'CustomerName',
    adr.ADDRESS1, 
    adr.ADDRESS2, 
    adr.ADDRESS3, 
    RTRIM(adr.CITY)+', '+RTRIM(adr.STATE)+' '+RTRIM(adr.ZIP) AS CSZ, RTRIM(adr.COUNTRY) AS 'Country',
    adr.CNTCPRSN, 
    '('+SUBSTRING(adr.PHONE1,1,3)+') '+SUBSTRING(adr.PHONE1,4,3)+'-'+SUBSTRING(adr.PHONE1,7,4) AS 'Phone',
    CASE adr.FAX 
    WHEN '' THEN ''
    ELSE '('+SUBSTRING(adr.FAX,1,3)+') '+SUBSTRING(adr.FAX,4,3)+'-'+SUBSTRING(adr.FAX,7,4) END AS 'FaxNo',
    MAX(l.QUANTITY) AS 'Qty', 
    (SELECT TOP 1 sl.DSCRIPTN FROM SVC00601 sl
    WHERE sl.CONTNBR=@ContractNo AND sl.CONSTS=l.CONSTS AND sl.LNSEQNBR=MIN(l.LNSEQNBR)) AS 'Desc1',
    (SELECT TOP 1 sl.SERLNMBR FROM SVC00601 sl
    WHERE sl.CONTNBR=@ContractNo AND sl.CONSTS=l.CONSTS AND sl.LNSEQNBR=MIN(l.LNSEQNBR)) AS 'Desc2',
    (SELECT TOP 1 sl.STRTDATE FROM SVC00601 sl
    WHERE sl.CONTNBR=@ContractNo AND sl.CONSTS=l.CONSTS AND sl.LNSEQNBR=MIN(l.LNSEQNBR)) AS 'StartDate',
    (SELECT TOP 1 sl.ENDDATE FROM SVC00601 sl
    WHERE sl.CONTNBR=@ContractNo AND sl.CONSTS=l.CONSTS AND sl.LNSEQNBR=MIN(l.LNSEQNBR)) AS 'EndDate',
    --MAX('Coverage Period: '+CAST(CAST(l.STRTDATE AS DATE) AS VARCHAR(20))+' - '+
    --CAST(CAST(l.ENDDATE AS DATE) AS VARCHAR(20))) AS 'Desc3',
    '' AS 'Desc3',
    (SELECT TOP 1 sn.NOTEINDX FROM SVC00601 sl 
    JOIN SY03900 sn ON sn.NOTEINDX=sl.NOTEINDX 
    WHERE sl.CONTNBR=@ContractNo AND sl.CONSTS=l.CONSTS AND sl.LNSEQNBR=MIN(l.LNSEQNBR))
    AS 'LineNoteIndx',
    '',
    SUM(l.ORIGTOTAL) AS 'LineTotal',
    MAX(h.ORIGTOTAL) AS 'Total',
RTRIM(curr.CRNCYSYM) AS 'CurrSymbol', 
RTRIM(e4F1.LONGNAME) AS 'TermsOfSale', 
RTRIM(e4F2.LONGNAME) AS 'PaymentTerms', 
RTRIM(e4F3.LONGNAME) AS 'Warranty', 
RTRIM(e4F4.LONGNAME) AS 'QuoteValidFor',
RTRIM(salespoc.SLPRSNFN) + ' ' + RTRIM(salespoc.SPRSNSLN) AS 'SalesPerson', 
@imageunc + 'signatures\' + RTRIM(salespoc.SLPRSNFN) + ' ' + RTRIM(salespoc.SPRSNSLN) + '.jpg'  AS 'SigImagePath', 
h.CONSTS,
h.NOTEINDX, 
''
FROM svc00600 h
LEFT JOIN RM00102 adr ON RTRIM(h.CUSTNMBR)=RTRIM(adr.custnmbr) AND h.ADRSCODE=adr.ADRSCODE
LEFT JOIN RM00101 cust ON RTRIM(cust.CUSTNMBR) = RTRIM(adr.CUSTNMBR)
INNER JOIN DYNAMICS..MC40200 curr ON curr.CURNCYID=h.CURNCYID
INNER JOIN SVC00601 l ON RTRIM(h.CONTNBR)=RTRIM(l.CONTNBR) AND h.CONSTS=l.consts
LEFT JOIN EXT00103 e103F1 ON e103F1.PT_UD_Key=h.CONTNBR AND e103F1.PT_UD_Number=1 AND e103F1.PT_Window_ID='CONTRACTINFO'
LEFT JOIN EXT40102 e4F1 ON e4F1.PT_Window_ID=e103F1.PT_Window_ID AND e4F1.Field_Number=1 AND e4F1.lnitmseq=e103F1.total
LEFT JOIN EXT00103 e103F2 ON e103F2.PT_UD_Key=h.CONTNBR AND e103F2.PT_UD_Number=2 AND e103F2.PT_Window_ID='CONTRACTINFO'
LEFT JOIN EXT40102 e4F2 ON e4F2.PT_Window_ID=e103F2.PT_Window_ID AND e4F2.Field_Number=2 AND e4F2.lnitmseq=e103F2.total
LEFT JOIN EXT00103 e103F3 ON e103F3.PT_UD_Key=h.CONTNBR AND e103F3.PT_UD_Number=3 AND e103F3.PT_Window_ID='CONTRACTINFO'
LEFT JOIN EXT40102 e4F3 ON e4F3.PT_Window_ID=e103F3.PT_Window_ID AND e4F3.Field_Number=3 AND e4F3.lnitmseq=e103F3.total
LEFT JOIN EXT00103 e103F4 ON e103F4.PT_UD_Key=h.CONTNBR AND e103F4.PT_UD_Number=4 AND e103F4.PT_Window_ID='CONTRACTINFO'
LEFT JOIN RM00301 salespoc ON salespoc.SLPRSNID = h.SLPRSNID
LEFT JOIN EXT40102 e4F4 ON e4F4.PT_Window_ID=e103F4.PT_Window_ID AND e4F4.Field_Number=4 AND e4F4.lnitmseq=e103F4.total
WHERE RTRIM(h.CONTNBR)=RTRIM(@ContractNo) 
AND (l.CONSTS=1 OR l.CONSTS=2)

GROUP BY l.CONFGREF, h.CUSTNMBR, cust.CUSTNAME, adr.ADDRESS1, adr.ADDRESS2, adr.ADDRESS3, adr.CITY, adr.STATE, adr.ZIP, adr.COUNTRY, adr.CNTCPRSN, adr.PHONE1, adr.FAX, h.CONTNBR, l.CONSTS, 
curr.CRNCYSYM, e4F1.LONGNAME, e4F2.LONGNAME, e4F3.LONGNAME, e4F4.LONGNAME, salespoc.SLPRSNFN, salespoc.SPRSNSLN, h.CONSTS, h.NOTEINDX
HAVING h.CONSTS=(SELECT MAX(CONSTS) FROM SVC00600 WHERE CONTNBR=RTRIM(@ContractNo))
ORDER BY min(l.LNSEQNBR) ASC

UPDATE r
SET Notes=ISNULL((SELECT TXTFIELD FROM SY03900 WHERE NOTEINDX=r.Noteindx),''),
LineNotes=ISNULL((SELECT TXTFIELD FROM SY03900 WHERE NOTEINDX=r.LineNoteindx),''),
Desc3='Coverage Period: '+CAST(CAST(StartDate AS DATE) AS VARCHAR(20))+' - '+CAST(CAST(EndDate AS DATE) AS VARCHAR(20))

FROM @rep r

--SELECT * FROM @rep

SELECT *, convert(int,rand()*3)+1 as rnd from @rep

END

Answer 1

Your Query is pretty long and complex to quickly understand, but from what I can see your table has 29 fields and in you INSERT query you only set 21. That's not allowed on SQL without specify the fields names to be updated or inserted. For instance if you have a table with 3 columns like this:

• Code
• Desc
• Qty

You can't use this:

INSERT INTO myTable SELECT 'myCode' as Code, 'myDesc' as Desc

Because you are missing the Qty field. Never the less, if the field Qty is nulleable, you can do this:

INSERT INTO myTable(Code, Desc) SELECT 'myCode' as Code, 'myDesc' as Desc

Hope this helps you.