Problem

I created a table and a store procedure a few days ago. In my store procedure, I used a temporary table with structure as same as the base table created.

Table

USE Demo
GO
IF OBJECT_ID(N’Employee’) IS NOT NULL
DROP TABLE Employee
GO
CREATE TABLE Employee
(
EmployeeID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Title nvarchar(50),
Email nvarchar(50),
Address nvarchar(150),
HiredDate date,
Salary decimal,
LastUpdate datetime,
CONSTRAINT PK_Employee PRIMARY KEY
(
EmployeeID
)
)

Store procedure

CREATE PROCEDURE usp_GetEmployeeHierarchy
AS
BEGIN
IF OBJECT_ID(N’tempdb..#tmpEmployee’) IS NOT NULL
DROP TABLE #tmpEmployee

CREATE TABLE #Employee
(
EmployeeID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Title nvarchar(50),
Email nvarchar(50),
Address nvarchar(150),
HiredDate date,
Salary decimal,
LastUpdate datetime
)

— Insert data into temp table
INSERT INTO #Employee
SELECT *
FROM Employee

/*
Do something here
*/
END

The store procedure was working smoothly until I have received the error message today as like that

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

After investigating, I realized  that structure of base table has been update by adding additional column LeaveHours.

Solution

Because the base table structure has been updated but temporary table is still old structure.  And I used SELECT *  statement in my store procedure. This causes the issue of inserting data into temporary table. It does not meet with structure of temporary table.

To avoid unexpected issue, I replaced SELECT *  with SELECT <columns list> explicitly.

ALTER PROCEDURE usp_GetEmployeeHierarchy
AS
BEGIN
IF OBJECT_ID(N’tempdb..#tmpEmployee’) IS NOT NULL
DROP TABLE #tmpEmployee
CREATE TABLE #Employee
(
EmployeeID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Title nvarchar(50),
Email nvarchar(50),
Address nvarchar(150),
HiredDate date,
Salary decimal,
LastUpdate datetime
)
— Insert data into temp table
INSERT INTO #Employee([EmployeeID]
,[FirstName]
,[LastName]
,[Title]
,[Email]
,[Address]
,[HiredDate]
,[Salary]
,[LastUpdate])
SELECT [EmployeeID]
,[FirstName]
,[LastName]
,[Title]
,[Email]
,[Address]
,[HiredDate]
,[Salary]
,[LastUpdate]
FROM Employee
/*
Do something here
*/
END

Conclusions

  • The best practice, we should avoid using SELECT * if it’s not necessary because we will encounter unexpected issues in our code as below example.
  • Should query necessary columns you want, remove unused columns in your code because it affects to query performance.  Let’s imagine, what will happen if you query all of columns  but do not use at all. Your system must not only spend more memory to store unused columns,  but also waste bandwidth of your network.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s