sqlserver,定义一个变量,但返回NULL,'SET @item_id' 导致出错,但不知为何 10C

DECLARE @user_id bigint
DECLARE @item_ids varchar(1000)
SET @user_id = 7
SET @item_ids = N'536563,562379'
DECLARE @i INT;
DECLARE @count INT;
DECLARE @price BIGINT;
DECLARE @total_price BIGINT;
DECLARE @temp_price BIGINT;
SET @total_price = 1;
SET @i = 0;
SET @count = (SELECT COUNT(VALUE) FROM string_split(@item_ids,',') );
WHILE (@i < @count)
BEGIN
DECLARE @item_id BIGINT;
SET @item_id = CAST(
(
SELECT ID FROM
(
SELECT CAST(VALUE AS BIGINT) AS ID,ROW_NUMBER() OVER(ORDER BY VALUE) AS RowNum
FROM string_split(@item_ids,',')) AS Idx
WHERE RowNum = @i
)
AS BIGINT
) ;
SET @price = (SELECT price FROM tb_item WHERE id = @item_id)
SET @total_price = @total_price + @price
SET @i = @i + 1
END
SELECT @total_price AS TTP

3个回答

一、SELECT可以在一条语句里对多个变量同时赋值,而SET只能一次对一个变量赋值

二、表达式返回多个值时,用SET将会出错,而SELECT将取最后一个值

三、表达式无返回值时,用SET将置变量值为NULL,用SELECT变量将保持原值

四、使用标量子查询时,如果无返回值,SET和SELECT一样,都将置为NULL

程序时不会骗人的,这个不报错那就不正常了,你先看看你定义的变量先,你定义SET @item_ids 你赋值时用的是@item_id 这是同一个变量么 ?

SET @item_id = CAST(
(
SELECT ID FROM
(
SELECT CAST(VALUE AS BIGINT) AS ID,ROW_NUMBER() OVER(ORDER BY VALUE) AS RowNum
FROM string_split(@item_ids,',')) AS Idx
WHERE RowNum = @i
)
AS BIGINT
) ;

你这段代码写得有问题,cast第一个参数不能是多行,你这里的select有可能查询出来的是多行,只能是查询出来一行一列的那种,比如select '10086',这样的。null和空没关系,都可以

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问