Sql-server – Column name or number of supplied values doesn not match table definition

sql serverstored-procedurest-sql

I have created three tables using

USE UserDecomAudit
GO
CREATE TABLE ADUserData
(
ADUserID int NOT NULL PRIMARY KEY,
Name varchar(50),
SamAccountName varchar(10) NOT NULL,
DistinguishedName varchar(200) NOT NULL,
HomeDirectory varchar(100),
OfficePhone varchar(20),
Extension varchar(4),
Manager varchar(50),
Title varchar(50),
Department varchar(50),
Company varchar(20),
DateOfAccountDecommissioning DATETIME NOT NULL
)

USE UserDecomAudit
GO
CREATE TABLE CSUserData
(
CSUserID int NOT NULL PRIMARY KEY,
ADUserID int NOT NULL,
SIPAddress varchar(50),
LineURI varchar(20),
EnterpriseVoiceEnabled varchar(3),
ExternalAccessPolicy varchar(50),
PersistentChatPolicy varchar(50)
)

USE UserDecomAudit
GO
CREATE TABLE ADGroups
(
GroupID int NOT NULL PRIMARY KEY,
ADUserID int NOT NULL,
ADGroup varchar (200)
)

And am trying to create a stored procedure using

USE UserDecomAudit
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.LogUserDetails
-- Add the parameters for the stored procedure here
@Name varchar(50)= null,
@SamAccountName varchar(10)= null,
@DistinguishedName varchar(200)= null,
@HomeDirectory varchar(100)= null,
@OfficePhone varchar(20)= null,
@Extension varchar(4)= null,
@Manager varchar(50)= null,
@Title varchar(50)= null,
@Department varchar(50)= null,
@Company varchar(20)= null,
@SIPAddress varchar(50)= null,
@LineURI varchar(20)= null,
@EnterpriseVoiceEnabled varchar(3)= null,
@ExternalAccessPolicy varchar(50)= null,
@PersistentChatPolicy varchar(50)= null,
@ADGroup1 varchar (200)= null,
@ADGroup2 varchar (200)= null,
@ADGroup3 varchar (200)= null,
@ADGroup4 varchar (200)= null,
@ADGroup5 varchar (200)= null
AS
BEGIN
DECLARE @ID INT

INSERT INTO dbo.ADUserData (Name,SamAccountName,Distinguishedname,HomeDirectory,OfficePhone,Extension,Manager,Title,Department,DateOfAccountDecommissioning)
VALUES (@Name,@SamAccountName,@Distinguishedname,@HomeDirectory,@OfficePhone,@Extension,@Manager,@Title,@Department,getdate())

SET @ID = cast(scope_identity() AS INT);

INSERT INTO dbo.CSUserData (ADUserID,SIPAddress,LineURI,EnterpriseVoiceEnabled,ExternalAccessPolicy,PersistentChatPolicy)
VALUES (@ID,@SIPAddress,@LineURI,@EnterpriseVoiceEnabled,@ExternalAccessPolicy,@PersistentChatPolicy)


WITH UserADGroups AS
(
    SELECT @ADGroup1 AS ADGroup UNION all
    SELECT @ADGroup2 UNION all
    SELECT @ADGroup3 UNION all
    SELECT @ADGroup4 UNION all
    SELECT @ADGroup5 

)
INSERT INTO dbo.ADGroups
SELECT @ID, ADGroup
FROM UserADGroups
WHERE ADGroup IS NOT NULL

END
GO

But I'm receiving an error saying column name or number of supplied values does not match the definition. I'm not sure what I've done wrong

Best Answer

dbo.ADGroups requieres 3 columns/values:

  • GroupID int NOT NULL PRIMARY KEY,
  • ADUserID int NOT NULL,
  • ADGroup varchar (200)

But your query only selects 2 values:

  • @ID
  • ADGroup

And SQL Server does not know which one is missing and what to do with the other 2.

You can add a third value for GroupId but it is a lot better to add the column names you are using after the table name when you insert something into a table:

INSERT INTO dbo.ADGroups(ADUserID, ADGroup)
SELECT @ID, ADGroup
FROM UserADGroups
WHERE ADGroup IS NOT NULL

You should always use this syntax with a list of column name: insert into table(colA, colB, ...). It will allow SQL Server to know which value belongs to which column.

Since GroupId cannot be null, it will fail as well. You then have 2 option:

  • Supply a valid GroupId value
  • Use Identity GroupID int identity(0, 1) NOT NULL PRIMARY KEY
Related Question