The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

Question

0.00/5 (No votes)

See more:

Hello everyone can you help me to figure out the error of my code stated :"The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns."

CODE:

SQL

ALTER PROCEDURE [dbo].[statuslevel]

@statuslevel bigint,
@status varchar(max)

AS 
BEGIN

select @statuslevel = 50;

--CASE WHEN <boolean> THEN <true> else status 
INSERT INTO tblsummaryIMPORT(status,statuslevel) 

   SELECT CASE WHEN @statuslevel=50 THEN 'process' 
		 WHEN @statuslevel=100  THEN 'approved' 
		 When @statuslevel=50 then 'for payment'
		 when @statuslevel<=200 then 'paid' 
	ELSE 
		case when @statuslevel<201 then 'cancelled'
			 when @statuslevel>=250 then 'for callout'
			ELSE
				case when @statuslevel<251 then 'for installation'
					 when @statuslevel>=300 then 'for rescheduling'
				ELSE
					case when @statuslevel<301 then 'scheduled-rescheduled'
					ELSE
						case when @statuslevel<302 then 'activated'
						ELSE
							case when @statuslevel<303 then 'cancel schedule' 
							ELSE
								case when @statuslevel<304 then 'failed closed-closed'
								ELSE
									case when @statuslevel<305 then 'cancelled-deployment'
									ELSE
										case when @statuslevel<306 then 'closed conversion canopy' 
											 when @statuslevel>=350 then 'for rescheduling'
										ELSE
											case when @statuslevel<351 then 'cancelled schedule'
												 when @statuslevel>=400 then 'activated for qa'
												 when @statuslevel>=450 then 'activated for payment' 
											
											
											else 
												null end
											end
										END
									END
								END
							END
						END
					END
				END
	END as status

Posted 6-Jul-13 0:25am

Member 10033107

Updated 6-Jul-13 3:09am

Maciej Los

v2

Add a Solution

3 solutions

Add a Solution

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)

Maciej Los · Answer 1 · 2013-07-06T03:23:00

Have a look at your sql code, there is bad logic!
Below condition is true:
@statuslevel<=200 AND @statuslevel=50
Do you know what i mean?

If you want to insert into existing table some values (statuslevel and corresponding to it status), try something like this:

SQL

DECLARE @tmp TABLE(statuslevel INT, [status] VARCHAR(255))
INSERT INTO @tmp (statuslevel, [status])
SELECT 50 AS statuslevel, 'process' AS [status]  -- statuslevel = 50
UNION ALL SELECT 100, 'approved' 
UNION ALL SELECT 50, 'for payment' --and another statuslevel = 50!!!
UNION ALL SELECT 200, 'paid' 
UNION ALL SELECT 201, 'cancelled'
UNION ALL SELECT 250, 'for callout'
UNION ALL SELECT 251, 'for installation'
UNION ALL SELECT 300, 'for rescheduling'
UNION ALL SELECT 301, 'scheduled-rescheduled'
UNION ALL SELECT 302, 'activated'
UNION ALL SELECT 303, 'cancel schedule' 
UNION ALL SELECT 304, 'failed closed-closed'
UNION ALL SELECT 305, 'cancelled-deployment'
UNION ALL SELECT 306, 'closed conversion canopy' 
UNION ALL SELECT 350, 'for rescheduling'
UNION ALL SELECT 351, 'cancelled schedule'
UNION ALL SELECT 400, 'activated for qa'
UNION ALL SELECT 450, 'activated for payment'

SELECT *
FROM @tmp

Member 10033107 · Answer 2 · 2013-07-09T17:30:00

hello everyone i just do another code for this because it will be more critical within my status of my program.. yet can you help me or share where i can put an insert statement here on dbo.tblsummaryimport (table) within the column 'STATUS' from the table

SQL

ALTER PROCEDURE [dbo].[statuslevel]

(@IDD BIGINT) as select  statuslevel, CSPSTATUS, CYBERYASTATUS from tblstatlevel


SET @IDD = ''
DECLARE @STATUSLEVEL BIGINT


SET @STATUSLEVEL = 1
SELECT
CASE
    WHEN @IDD = 1 THEN 'FOR PAYMENT'
    WHEN @IDD = 2 THEN 'APPROVED'
        ELSE NULL END
SET @STATUSLEVEL = 2
SELECT
CASE
    WHEN @IDD BETWEEN 3 AND 5 THEN 'FOR INSTALLATION'
        ELSE NULL END
SET @STATUSLEVEL = 3
SELECT
CASE
    WHEN @IDD BETWEEN 6 AND 8 THEN 'FOR PC DELIVERY'
        ELSE NULL END


SET @STATUSLEVEL = 4
SELECT
CASE
    WHEN @IDD = 9 THEN 'INSTALLED COMPLETE'
        ELSE NULL END
SET @STATUSLEVEL = 5
SELECT
CASE
    WHEN @IDD BETWEEN 10 AND 11 THEN 'EXISTING'
        ELSE NULL END
SET @STATUSLEVEL = 6
SELECT
CASE
    WHEN @IDD = 12 THEN 'FOR SETTLEMENT OF OVERDUE BALANCE'
        ELSE NULL END
SET @STATUSLEVEL = 7
SELECT
CASE
    WHEN @IDD = 13 THEN 'FAILED CLOSED'
        ELSE NULL END
SET @STATUSLEVEL = 8
SELECT
CASE
    WHEN @IDD BETWEEN 14 AND 15 THEN 'CANCELLED'
        ELSE NULL END
SET @STATUSLEVEL = 9
SELECT
CASE
    WHEN @IDD = 16 THEN 'NOT COVERED'
        ELSE NULL END
SET @STATUSLEVEL =10
SELECT
CASE
    WHEN @IDD = 17 THEN 'BLACKLISTED'
        ELSE NULL END

_Asif_ · Answer 3 · 2013-07-06T01:49:00

The problem is you are passing only one parameter named Status to insert sql, statuslevel is missing from your select statement.

Modify your sql in below format. This could solve your problem.

SQL

INSERT INTO tblsummaryIMPORT(status,statuslevel)
SELECT CASE WHEN @statuslevel=50 THEN 'process'
WHEN @statuslevel=100 THEN 'approved'
When @statuslevel=50 then 'for payment'
when @statuslevel<=200 then 'paid'
ELSE
null end as status, 1 as Statuslevel

Thanks

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0