SQL Server While Statement error

Question

I run the following query like this :

WHILE (SELECT CONVERT(NVARCHAR, disburse_date, 112) FROM TEST_LOOP) <> 201701
BEGIN
    UPDATE TEST_LOOP
    SET disburse_date = DATEADD(DD, 14, disburse_date)

    SELECT * FROM TEST_LOOP

    IF (SELECT CONVERT(NVARCHAR, disburse_date, 112) FROM TEST_LOOP) = 201701
        BREAK
END

And I get the following error :

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

I've tried to evaluate my query and not yet solved. Any advice will be appreciate.

Thanks..

Your code has so many problems, there is no place to begin. Ask another question and (1) provide sample data; (2) show the results you want; and (3) explain what you are trying to do. — Gordon Linoff
– Gordon Linoff, Commented Feb 17, 2017 at 2:58
Are you trying to update all records in the table so all of them have disburse_date reaching Jan2017? — ydoow
– ydoow, Commented Feb 17, 2017 at 3:11

ydoow · Accepted Answer · 2017-02-17 03:20:40Z

1

Assuming you want to update all records so the disburse_date is reaching Jan2017.

To make it more readable I would create a function first.

-- function to return a date reaching Jan2017 with an incremental of 14 days from input date
CREATE FUNCTION fn_DateReaching201701
(    
    @in_date AS DateTime    
) 
RETURNS DateTime 
AS
BEGIN

    DECLARE @out_date AS DateTime
    SET @out_date = @in_date

    WHILE (CONVERT(NVARCHAR(6),@out_date,112) != 201701) 
    BEGIN
            set @out_date = DATEADD(day, 14, @out_date)
    END

    RETURN @out_date
END

Then, apply normal update statement. The additional WHERE clause is to make sure all records are prior Jan2017 to avoid an infinite loop.

UPDATE TEST_LOOP
SET disburse_date =  dbo.fn_DateReaching201701(disburse_date)
WHERE disburse_date < '2017-02-01'

answered Feb 17, 2017 at 3:20

ydoow

2,9964 gold badges27 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2017-02-17 02:59:58Z

0

Is this what you want to do?

UPDATE TEST_LOOP
    SET TGL_AWAL_KREDIT = DATEADD(DAY, 14, TGL_AWAL_KREDIT)
    WHERE TGL_AWAL_KREDIT < '20170101';

Note: There is no WHILE loop.

answered Feb 17, 2017 at 2:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Afif Pratama Over a year ago

I want to loop the day until reach January 2017. Date sample in TGL_AWAL_KREDIT field is '2015-05-27 00:00:00.000'.

Gordon Linoff Over a year ago

@AfifPratama . . . There is no reason to loop, if a set-based approach solves the problem.

Collectives™ on Stack Overflow

SQL Server While Statement error

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related