How to set a variable - perform an action - and then set the variable to + 1

[bmacbmac]

Hello.

Trying something like this. I am trying to set a @variable to 1 so I can update some records to FOLDER1. At the end of my script I would like to set the @variable to + 1 so I can set the next records to FOLDER2, etc.

How can I set a starting @variable and then add + 1 to it? When the script loops around the @variable is reset to 1.

while (select count(id) from mytable where folder is null) > 0
begin
declare @foldernumber int
set @foldernumber = 1

update mytable set folder = 'FOLDER' + convert(varchar(5), @foldernumber)

set @foldernumber = @foldernumber + 1
end

Any ideas? I'm sure there is a way to do this.

 

[bmacbmac]

oh wait.. i think I figured it out by setting the starting number before the while loop.

declare @foldernumber int
set @foldernumber = 1

while (select count(id) from mytable where folder is null) > 0
begin

update mytable set folder = 'FOLDER' + convert(varchar(5), @foldernumber)

set @foldernumber = @foldernumber + 1
end

 

[DaveInIowa]

A couple things to note; you'll never do more than 1 pass because you are updating all mytable rows with a non-null folder value and there are a couple shortcuts you could use

DECLARE @foldernumber int = 1 -- [highlight #FCE94F]Declare and initialize on 1 statement[/highlight]
WHILE (EXISTS (SELECT 1 FROM mytable WHERE folder IS NULL)) BEGIN

	UPDATE mytable
	   SET folder = 'FOLDER' + CONVERT(VARCHAR(5), @foldernumber)
	 WHERE someColumn = @foldernumber -- [highlight #FCE94F]Are you missing something like this?[/highlight]

	SET @foldernumber += 1 -- [highlight #FCE94F]Shorthand for incrementing a value[/highlight]
END

EDIT: And if the folder number can be determined by a column value, there's no need for a loop

UPDATE mytable
   SET folder = 'FOLDER' + CAST(someColumn AS VARCHAR)
 WHERE folder IS NULL