Msg 213, Level 16, State 1, Procedure SP_TEST_TAT_2, Line 180 in Store Procedure SQL Server 2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

Please help, i already create store procedure in SQL server 2005.
But there was an error, the message was

SQL

Msg 213, Level 16, State 1, Procedure SP_TEST_TAT_2, Line 180
Insert Error: Column name or number of supplied values does not match table definition.

I don't know again how to solve it, I'm programmer AS/400 who learn SQL server. I never create store procedure before. Thanks for all your help!!

This is the code :

SQL

USE [test3]
GO
/****** Object:  StoredProcedure [dbo].[SP_TEST_TAT]    Script Date: 06/26/2013 13:56:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Unnamed
-- Create date: 26/06/2013
-- Description:	This store procedure is for dashboard report for Claim
-- =============================================
--CREATE PROCEDURE [dbo].[SP_TEST_TAT]
ALTER PROCEDURE [dbo].[SP_TEST_TAT_2]
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @temp TABLE
	(
		ID INT IDENTITY(1,1)
		,APPROVE_DATE VARCHAR(20)
		,[TAT_<=_5_DAYS] FLOAT
		,[COUNT_TAT_<=_5_DAYS] FLOAT	
		,[TAT_6-10_DAYS] FLOAT
		,[COUNT_TAT_6-10_DAYS] FLOAT	
		,[TAT_11-15_DAYS] FLOAT
		,[COUNT_TAT_11-15_DAYS] FLOAT	
		,[TAT_16-20_DAYS] FLOAT
		,[COUNT_TAT_16-20_DAYS] FLOAT	
		,[TAT_>_20_DAYS] FLOAT
		,[COUNT_TAT_>_20_DAYS] FLOAT	
	)

	INSERT INTO @temp
	SELECT LEFT(APPROVE_DATE,6)
	,[TAT_<=_5_DAYS]
	,CASE WHEN [TAT_<=_5_DAYS] >0 THEN 1 ELSE 0 END AS [COUNT_TAT_<=_5_DAYS]
	,[TAT_6-10_DAYS]
	,CASE WHEN [TAT_6-10_DAYS] >0 THEN 1 ELSE 0 END AS [COUNT_TAT_6-10_DAYS]
	,[TAT_11-15_DAYS]
	,CASE WHEN [TAT_11-15_DAYS] >0 THEN 1 ELSE 0 END AS [COUNT_TAT_11-15_DAYS]
	,[TAT_16-20_DAYS]
	,CASE WHEN [TAT_16-20_DAYS] >0 THEN 1 ELSE 0 END AS [COUNT_TAT_16-20_DAYS]
	,[TAT_>_20_DAYS]
	,CASE WHEN [TAT_>_20_DAYS] >0 THEN 1 ELSE 0 END AS [COUNT_TAT_>_20_DAYS]
	FROM [test3].[dbo].[TAT_db2] WITH(NOLOCK)
	--WHERE LEFT(APPROVE_DATE,6) = '201211'

	DECLARE @Result1 TABLE
	(
		ID INT IDENTITY(1,1)
		,APPROVE_DATE VARCHAR(20)
		,[TAT_<=_5_DAYS] FLOAT
		,[COUNT_TAT_<=_5_DAYS] FLOAT	
		,[TAT_6-10_DAYS] FLOAT
		,[COUNT_TAT_6-10_DAYS] FLOAT	
		,[TAT_11-15_DAYS] FLOAT
		,[COUNT_TAT_11-15_DAYS] FLOAT	
		,[TAT_16-20_DAYS] FLOAT
		,[COUNT_TAT_16-20_DAYS] FLOAT	
		,[TAT_>_20_DAYS] FLOAT
		,[COUNT_TAT_>_20_DAYS] FLOAT	
		,[Total_TAT_Claim] FLOAT
		,[Total_Claim] FLOAT
		,[Total_%_Claims] FLOAT
		,[Total_Weighted_TAT] FLOAT
	)
	INSERT INTO @Result1
	SELECT APPROVE_DATE
		,SUM([TAT_<=_5_DAYS]) AS [TAT_<=_5_DAYS]
		,SUM([COUNT_TAT_<=_5_DAYS] ) AS [COUNT_TAT_<=_5_DAYS]	
		,SUM([TAT_6-10_DAYS]) AS [TAT_6-10_DAYS] 
		,SUM([COUNT_TAT_6-10_DAYS]) AS [COUNT_TAT_6-10_DAYS]
		,SUM([TAT_11-15_DAYS]) AS [TAT_11-15_DAYS]  
		,SUM([COUNT_TAT_11-15_DAYS]) AS [COUNT_TAT_11-15_DAYS]  
		,SUM([TAT_16-20_DAYS]) AS [TAT_16-20_DAYS] 
		,SUM([COUNT_TAT_16-20_DAYS]) AS [COUNT_TAT_16-20_DAYS] 
		,SUM([TAT_>_20_DAYS] ) AS [TAT_>_20_DAYS] 
		,SUM([COUNT_TAT_>_20_DAYS]) AS [COUNT_TAT_>_20_DAYS] 
		,(SUM([TAT_<=_5_DAYS]) + SUM([TAT_6-10_DAYS]) + SUM([TAT_11-15_DAYS]) + SUM([TAT_16-20_DAYS]) +SUM([TAT_>_20_DAYS] ) )  AS [Total_TAT_Claim]
		,(SUM([COUNT_TAT_<=_5_DAYS]) + SUM([COUNT_TAT_6-10_DAYS]) + SUM([COUNT_TAT_11-15_DAYS]) + SUM([COUNT_TAT_16-20_DAYS]) +SUM([COUNT_TAT_>_20_DAYS] ) )  AS [Total_Claim]
		,(SUM([TAT_<=_5_DAYS]) + SUM([TAT_6-10_DAYS]) + SUM([TAT_11-15_DAYS]) + SUM([TAT_16-20_DAYS]) +SUM([TAT_>_20_DAYS])) / (SUM([COUNT_TAT_<=_5_DAYS]) + SUM([COUNT_TAT_6-10_DAYS]) + SUM([COUNT_TAT_11-15_DAYS]) + SUM([COUNT_TAT_16-20_DAYS]) + SUM([COUNT_TAT_>_20_DAYS] ) )  AS [Total_%_Claims]
		,(SUM([TAT_<=_5_DAYS]) + SUM([TAT_6-10_DAYS]) + SUM([TAT_11-15_DAYS]) + SUM([TAT_16-20_DAYS]) +SUM([TAT_>_20_DAYS])) / (SUM([COUNT_TAT_<=_5_DAYS]) + SUM([COUNT_TAT_6-10_DAYS]) + SUM([COUNT_TAT_11-15_DAYS]) + SUM([COUNT_TAT_16-20_DAYS]) + SUM([COUNT_TAT_>_20_DAYS] ) )  AS [Total_Weighted_TAT]
		
	FROM @temp
	GROUP BY APPROVE_DATE

	--SELECT * FROM @Result1

	DECLARE @tempApproveDate TABLE
	(
		ID INT IDENTITY(1,1)
		,APPROVE_DATE VARCHAR(6)
	)

	INSERT INTO @tempApproveDate
	SELECT DISTINCT APPROVE_DATE FROM @Result1

	DECLARE @Result TABLE
	(
		ID INT IDENTITY(1,1)
		,APPROVE_DATE VARCHAR(20)
		,REMARKS VARCHAR(200)
		,[TAT_<=_5_DAYS] FLOAT
		,[TAT_6-10_DAYS] FLOAT
		,[TAT_11-15_DAYS] FLOAT
		,[TAT_16-20_DAYS] FLOAT	
		,[TAT_>_20_DAYS] FLOAT	
		,TOTAL FLOAT
	)

	DECLARE @IDApproveDate INT,@ApproveDate VARCHAR(6)
	SET @IDApproveDate = (SELECT TOP 1 ID FROM @tempApproveDate)
	WHILE @IDApproveDate IS NOT NULL
	BEGIN
		SET @ApproveDate = (SELECT APPROVE_DATE FROM @tempApproveDate WHERE ID=@IDApproveDate)
		
		INSERT INTO @Result
		SELECT @ApproveDate,'Total TAT',[TAT_<=_5_DAYS],[TAT_6-10_DAYS],[TAT_11-15_DAYS],[TAT_16-20_DAYS],[TAT_>_20_DAYS],[Total_TAT_Claim] 			
		FROM @Result1 WHERE APPROVE_DATE = @ApproveDate

		INSERT INTO @Result
		SELECT @ApproveDate,'No Of Claims',[COUNT_TAT_<=_5_DAYS],[COUNT_TAT_6-10_DAYS],[COUNT_TAT_11-15_DAYS], [COUNT_TAT_16-20_DAYS],[COUNT_TAT_>_20_DAYS],[Total_Claim] 
		FROM @Result1 WHERE APPROVE_DATE = @ApproveDate
		
		INSERT INTO @Result
		SELECT @ApproveDate,'% Claims',ISNULL([COUNT_TAT_<=_5_DAYS]/NULLIF([Total_Claim],0),0)*100 AS [%_TAT_<=_5_DAYS]	 
						   ,ISNULL([COUNT_TAT_6-10_DAYS]/NULLIF([Total_Claim],0),0)*100 AS [%_TAT_6-10_DAYS]	 	
						   ,ISNULL([COUNT_TAT_11-15_DAYS]/NULLIF([Total_Claim],0),0)*100 AS [%_TAT_11-15_DAYS]	 			
						   ,ISNULL([COUNT_TAT_16-20_DAYS]/NULLIF([Total_Claim],0),0)*100 AS [%_TAT_16-20_DAYS]
						   ,ISNULL([COUNT_TAT_>_20_DAYS]/NULLIF([Total_Claim],0),0)*100 AS [%_TAT_>_20_DAYS]
						   ,[Total_%_Claims] AS [TOTAL_%_Claims]
		FROM @Result1 WHERE APPROVE_DATE = @ApproveDate
		
		INSERT INTO @Result
		SELECT @ApproveDate,'Weighted TAT',ISNULL([TAT_<=_5_DAYS]/NULLIF([COUNT_TAT_<=_5_DAYS],0),0) AS [Weighted_TAT_<=_5_DAYS]	 
							,ISNULL([TAT_6-10_DAYS]/NULLIF([COUNT_TAT_6-10_DAYS],0),0) AS [Weighted_TAT_6-10_DAYS]	 
							,ISNULL([TAT_11-15_DAYS]/NULLIF([COUNT_TAT_11-15_DAYS],0),0) AS [Weighted_TAT_11-15_DAYS]	 
							,ISNULL([TAT_16-20_DAYS]/NULLIF([COUNT_TAT_16-20_DAYS],0),0) AS [Weighted_TAT_16-20_DAYS]	 
							,ISNULL([TAT_>_20_DAYS]/NULLIF([COUNT_TAT_>_20_DAYS],0),0) AS [Weighted_TAT_>_20_DAYS]	
							,ISNULL([Total_TAT_Claim]/NULLIF([Total_Claim],0),0) AS [Weighted_TAT_TOTAL]	
		FROM @Result1 WHERE APPROVE_DATE = @ApproveDate


		SET @IDApproveDate = (SELECT TOP 1 ID FROM @tempApproveDate WHERE ID>@IDApproveDate)
	END

-- THIS STATEMENT USE TO INSERT THE TOTAL BESIDES THE TAT > 20 DAYS
--	DECLARE @IDApproveDate_2 INT,@ApproveDate VARCHAR(6)
--	SET @IDApproveDate = (SELECT TOP 1 ID FROM @tempApproveDate)

--	DECLARE @Result2 TABLE
--	(
--		ID INT IDENTITY(1,1)
--		,APPROVE_DATE VARCHAR(20)
--		,REMARKS VARCHAR(200)
--		,[TAT_<=_5_DAYS] FLOAT
--		,[TAT_6-10_DAYS] FLOAT
--		,[TAT_11-15_DAYS] FLOAT
--		,[TAT_16-20_DAYS] FLOAT	
--		,[TAT_>_20_DAYS] FLOAT
--		,TOTAL FLOAT
--
--	)
--
--	SET @IDApproveDate = (SELECT TOP 1 ID FROM @tempApproveDate)
--	WHILE @IDApproveDate IS NOT NULL
--	BEGIN
--		SET @ApproveDate = (SELECT APPROVE_DATE FROM @tempApproveDate WHERE ID=@IDApproveDate)
--
--		INSERT INTO @Result2
--		SELECT @ApproveDate,REMARKS,[TAT_<=_5_DAYS],[TAT_6-10_DAYS],[TAT_11-15_DAYS],[TAT_16-20_DAYS],[TAT_>_20_DAYS],SUM([TAT_<=_5_DAYS] + [TAT_6-10_DAYS] + [TAT_11-15_DAYS] + [TAT_16-20_DAYS] + [TAT_>_20_DAYS]) AS TOTAL
--
--		FROM @Result WHERE APPROVE_DATE = @ApproveDate 
--		GROUP BY APPROVE_DATE,REMARKS,[TAT_<=_5_DAYS],[TAT_6-10_DAYS],[TAT_11-15_DAYS],[TAT_16-20_DAYS],[TAT_>_20_DAYS]
--
--		SET @IDApproveDate = (SELECT TOP 1 ID FROM @tempApproveDate WHERE ID>@IDApproveDate)
--	END
--	
--	Set IDENTITY_INSERT TEMP_TAT_2 ON

	DELETE FROM TEMP_TAT_2
	INSERT INTO TEMP_TAT_2
	SELECT APPROVE_DATE
	,REMARKS
	,[TAT_<=_5_DAYS]
	,[TAT_6-10_DAYS]
	,[TAT_11-15_DAYS]
	,[TAT_16-20_DAYS]
	,[TAT_>_20_DAYS] 
	,TOTAL 
	FROM @Result

	SELECT APPROVE_DATE
	,REMARKS
	,[TAT_<=_5_DAYS]
	,[TAT_6-10_DAYS]
	,[TAT_11-15_DAYS]
	,[TAT_16-20_DAYS]
	,[TAT_>_20_DAYS]
	,TOTAL 
  FROM TEMP_TAT WITH(NOLOCK)
END

Posted 26-Jun-13 0:05am

Purwo Susanto

Updated 26-Jun-13 0:40am

Maciej Los

v2

Add a Solution

Comments

[no name] 26-Jun-13 6:29am

The error message, "Column name or number of supplied values does not match table definition" is perfectly clear is it not? You have a column name that is not right or the supplied values do not match your table.

RedDk 26-Jun-13 14:06pm

The table has fifteen (plus index) and insert has only fourteen (ignore index in this case because that would be an automation using IDENTITY). Add another data item. Or check the target table and reduce it's items by one.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)