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

Question

Post reply

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

vigneshlagoons

SSChasing Mays

Points: 643
More actions
October 17, 2013 at 7:45 am

#299323

Hi All,
I have a table names Alert_Event and a new column named BSP_Phone has been added to the table. I am trying to set NULL values to the column and I get the below error. I am setting null values in the bolded text in the query.
Error Message:
Msg 213, Level 16, State 1, Procedure SaveBSPOutageInfo, Line 22
Column name or number of supplied values does not match table definition.
USE [gg]
GO
/****** Object: StoredProcedure [dbo].[SaveBSPOutageInfo] Script Date: 10/17/2013 19:01:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SaveBSPOutageInfo] @eventCreatedDate DATETIME, @eventOrigin varchar(10),
@eventType varchar(10), @alert_Type varchar(10), @outageComponent varchar(10),
@remedyTicket varchar(15), @Severity_Cd varchar(2), @Status_Cd varchar(2),
@Planned_Outage_Ind varchar(1), @Outage_Start_Time DATETIME, @Impacted_800_Num varchar(200),
@Bus_Hrs_Ind varchar(1),
@Expected_End_Time DATETIME, @Actual_End_Time DATETIME, @Addl_Steps varchar(2000), @BSP_Name varchar(30),
@Caller varchar(30), @Phone varchar(12), @Internal_Comments varchar(2000), @Internal_Summary varchar(255),
@Issue_Desc varchar(2000), @TimetoServicetheCustomer varchar(20), @QualityofService varchar(20), @AvailabilityofTools varchar(200),
@SystemLatencies varchar(200), @Addl_Notes varchar(2000), @id int output
AS
BEGIN
Insert into Alert_Event values(@eventCreatedDate, @eventOrigin, @eventType, @alert_Type, @outageComponent, @remedyTicket, @Severity_Cd, @Status_Cd, @Planned_Outage_Ind, @Outage_Start_Time, @Impacted_800_Num, @Bus_Hrs_Ind, @Expected_End_Time, @Actual_End_Time, @Addl_Steps, @BSP_Name, @Caller, @Phone, @Internal_Comments, @Internal_Summary, @Issue_Desc, @TimetoServicetheCustomer, @QualityofService, @AvailabilityofTools, @SystemLatencies, @Addl_Notes,NULL,NULL,NULL,NULL)
set @id=SCOPE_IDENTITY()
Insert into Alert_IncMgmt values (@id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'CHQ00000')
Insert into Alert_ProbMgmt values (@id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
RETURN @id
END
GO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286589 More actions · Answer 1

Let's start with formatting this sql into something legible.

ALTER PROCEDURE [dbo].[SaveBSPOutageInfo] @eventCreatedDate DATETIME

,@eventOrigin VARCHAR(10)

,@eventType VARCHAR(10)

,@alert_Type VARCHAR(10)

,@outageComponent VARCHAR(10)

,@remedyTicket VARCHAR(15)

,@Severity_Cd VARCHAR(2)

,@Status_Cd VARCHAR(2)

,@Planned_Outage_Ind VARCHAR(1)

,@Outage_Start_Time DATETIME

,@Impacted_800_Num VARCHAR(200)

,@Bus_Hrs_Ind VARCHAR(1)

,@Expected_End_Time DATETIME

,@Actual_End_Time DATETIME

,@Addl_Steps VARCHAR(2000)

,@BSP_Name VARCHAR(30)

,@Caller VARCHAR(30)

,@Phone VARCHAR(12)

,@Internal_Comments VARCHAR(2000)

,@Internal_Summary VARCHAR(255)

,@Issue_Desc VARCHAR(2000)

,@TimetoServicetheCustomer VARCHAR(20)

,@QualityofService VARCHAR(20)

,@AvailabilityofTools VARCHAR(200)

,@SystemLatencies VARCHAR(200)

,@Addl_Notes VARCHAR(2000)

,@id INT OUTPUT

AS

BEGIN

INSERT INTO Alert_Event

VALUES (

@eventCreatedDate

,@eventOrigin

,@eventType

,@alert_Type

,@outageComponent

,@remedyTicket

,@Severity_Cd

,@Status_Cd

,@Planned_Outage_Ind

,@Outage_Start_Time

,@Impacted_800_Num

,@Bus_Hrs_Ind

,@Expected_End_Time

,@Actual_End_Time

,@Addl_Steps

,@BSP_Name

,@Caller

,@Phone

,@Internal_Comments

,@Internal_Summary

,@Issue_Desc

,@TimetoServicetheCustomer

,@QualityofService

,@AvailabilityofTools

,@SystemLatencies

,@Addl_Notes

,NULL

)

SET @id = SCOPE_IDENTITY()

INSERT INTO Alert_IncMgmt

VALUES (

@id

,NULL

,'CHQ00000'

)

INSERT INTO Alert_ProbMgmt

VALUES (

@id

,NULL

)

RETURN @id

END

Now here is the problem. We don't know what your tables look like and your insert statement does not define the columns. That means we can't do a lot here to help. It seems to me that you have either not enough columns in your values or too many. I would recommend explicitly listing your columns and then providing values for only the columns listed. That will eliminate the nonsense of trying to figure out how many NULLs to put together. More importantly it will future proof your code. This is in a stored procedure. If you change the table your proc will be broken. If however, you define the columns in your insert, your proc will go along just fine if you add columns to that table.

You should do the same for all of your inserts.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

vigneshlagoons SSChasing Mays Points: 643 More actions · Answer 2

vigneshlagoons

SSChasing Mays

Points: 643

October 17, 2013 at 8:22 am

#1659342

Got it sean! Thanks