INSERT语句的选择列表包含的项目少于插入列表。 SELECT值的数量必须与INSERT列的数量匹配。 [英] 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.

问题描述

大家好，我们可以帮助我找出我所说的代码错误：INSERT语句的选择列表包含的项目少于插入列表.SELECT值的数量必须与INSERT列的数量相匹配。< br $> b $ b

代码：

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:

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

推荐答案

有看看你的sql代码，有错误的逻辑！

以下条件为真：

@ statuslevel< = 200 AND @ statuslevel = 50

你知道我的意思吗？



如果你想在现有表中插入一些值（statuslevel和对应它的状态），尝试这样的事情：

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:

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

大家好我只是为此做另一个代码，因为它在我的程序状态中会更加重要..但你可以帮我或分享我可以在db上放置一个插入语句的地方o.tblsummaryimport（表格）在表格'STATUS'栏目中

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

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

问题是您只传递一个参数命名状态以插入sql，select语句中缺少statuslevel。



以下面的格式修改你的sql。这可以解决你的问题。

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.

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

这篇关于INSERT语句的选择列表包含的项目少于插入列表。 SELECT值的数量必须与INSERT列的数量匹配。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！