Subquery returned more than 1 value. This is not permitted when the subquery follows =, ! =, <, <= , >, >= or when the subquery is used as an expression.

See more:

SQL

data not coming from the database while using the cursor query...

error at


SET @SPRICE=(SELECT RATE FROM MT_PSL_CUSTTDRATES_ITEM WHERE FROMDATE<=@TDATE AND
TODATE >=@TDATE AND
CCODE=@CCODE AND BCODE=@BCODE)

What I have tried:

here my procedure
__________________

ALTER PROCEDURE [dbo].[SDSUSPPSL_CUSTTDRATES_ITEM]
(
@MODE INT=4, -- 1 FOR INSERT, 2 FOR UPDATE, 3 FOR SELECT SPECIFIC, 4 FOR SELECT ALL, 5 FOR DELETE SPECIFIC, 6 FOR DELETE ALL
@SLNO NUMERIC(18,0) = NULL,
@CCODE INT = NULL,
@BCODE INT = NULL,
@ICODE INT=NULL,
@RATE NUMERIC(18,4) = NULL,
@LEAKS NUMERIC(18,2) = NULL,
@CRDATE DATETIME = NULL,
@CREATEDBY VARCHAR(50)=NULL,
@FROMDATE DATETIME=NULL,
@TODATE DATETIME=NULL,
@TDATE DATETIME=NULL,
@OUTPUT NUMERIC(18,2)=NULL OUTPUT


)
AS
DECLARE @RESULT INT

DECLARE @ITEMID INT
DECLARE @ITEMNAME VARCHAR(50)
DECLARE @SPRICE NUMERIC(18,2)



SET @RESULT=0

IF @MODE=11
BEGIN

DECLARE @TMP_ITEMS2 TABLE(
ITEMID INT,
ITEMNAME VARCHAR(50),
SPRICE NUMERIC(18,2),
ORDERNO INT
);
DECLARE CUR_ITEMS CURSOR FOR SELECT ITEMID,ITEMNAME,SPRICE
FROM MT_PSL_ITEMS M
OPEN CUR_ITEMS
FETCH NEXT FROM CUR_ITEMS INTO @ITEMID,@ITEMNAME,@SPRICE
WHILE @@FETCH_STATUS=0
BEGIN
SET @SPRICE=(SELECT RATE FROM MT_PSL_CUSTTDRATES_ITEM WHERE FROMDATE<=@TDATE AND
TODATE >=@TDATE AND
CCODE=@CCODE AND BCODE=@BCODE)

if(@sprice>0)
INSERT INTO @TMP_ITEMS2 SELECT @ITEMID,@ITEMNAME,@SPRICE,ORDERNO
FROM MT_PSL_ITEMS M
WHERE ITEMID=@ITEMID
FETCH NEXT FROM CUR_ITEMS INTO @ITEMID,@ITEMNAME,@SPRICE
END
CLOSE CUR_ITEMS
DEALLOCATE CUR_ITEMS
SELECT T.ITEMID,T.ITEMNAME,T.SPRICE RATE,ORDERNO
FROM @TMP_ITEMS2 T
ORDER BY ORDERNO
END
SELECT @RESULT