Get value depending of current iteration on while loop

user_14874931 0
Viewed 370

Currently I'm using SQL Server Management Studio v18, and I want to iterate a Table Type using a while loop, so I do:

CREATE TYPE [VarcharIdTableType] AS TABLE
(
    [FileName] VARCHAR(MAX)
)

Then I use a while loop as:

DECLARE @FileTableType [VarcharIdTableType]

INSERT INTO @FileTableType 
VALUES ('test1'),('test2'),('test3')

DECLARE @FileCount INT = (SELECT COUNT(*) FROM @FileTableType), @RowCount INT = 0

WHILE @RowCount <= @FileCount
BEGIN                  
    DECLARE @Content VARCHAR(555)= CONCAT('File name ', @filename)

    SET @RowCount = @RowCount + 1;
END

So I want to get each FileName depending of iteration number, how can I achieve that?

tsql sql-server sql
1 Answers

IMPORTANT: Do you actually need a loop? As you haven't shown us what your desired final outcome is we are unable to comment. But in many cases a set based approach will accomplish what you need and if so it should be used.

Making a few assumptions, such as no ID column in your table, and being able to delete rows from your table variable, I would do the following using exists rather than counting rows.

DECLARE @FileTableType TABLE ([Name] VARCHAR(MAX));

INSERT INTO @FileTableType([Name]) VALUES ('test1'), ('test2'), ('test3');

DECLARE @Content VARCHAR(MAX) = '', @Filename VARCHAR(MAX);

WHILE EXISTS (SELECT 1 FROM @FileTableType)
BEGIN
    SELECT TOP 1 @Filename = [Name] FROM @FileTableType;

    SET @Content = CONCAT('File number ', @Filename);

    DELETE FROM @FileTableType WHERE [Name] = @Filename;
END;

However having a known unique identifier on the values in the table variable would be better because if you have duplicate filenames the first code will have issues.

DECLARE @FileTableType TABLE (id INT IDENTITY(1,1), [Name] VARCHAR(MAX));

INSERT INTO @FileTableType([Name]) VALUES ('test1'), ('test2'), ('test3');

DECLARE @Content VARCHAR(MAX) = '', @Filename VARCHAR(555), @FileId INT;

WHILE EXISTS (SELECT 1 FROM @FileTableType)
BEGIN
    SELECT TOP 1 @FileId = id, @Filename = [Name] FROM @FileTableType;

    SET @Content = CONCAT('File number ', @Filename);

    DELETE FROM @FileTableType WHERE id = @FileId;
END;

Note the best practices:

  • Terminating all statements with a semi-colon
  • Listing all columns when inserting
  • Using VARCHAR(MAX) for @Content since the filename is.
user_11274280
Related