SQL loop invalid because no scalar variable?

Question

I'm trying to populate my database table with some dummy data and I wrote a loop for that. I'm using DBeaver to connect to my SQL Server db and I run the code against it.

DECLARE @counter INT = 1;

WHILE @counter <= 1000
BEGIN
    INSERT INTO dbo.Articles
        (ArticleCode, ArticleName)
    VALUES
        ('ARTCODE' + CAST(@counter AS VARCHAR), 'Name' + CAST(@counter AS VARCHAR))
    ;
    
    SET @counter = @counter + 1;
END;

For some reason, I get an error on line 3 (on the WHILE loop).

SQL Error [137] [S0002]: Must declare the scalar variable "@counter"

What did I do wrong?

Answer 1

The problem is DBeaver, rather than your SQL. DBeaver, when used with T-SQL, sees a semicolon (;) as a batch separator not a statement terminator, despite the fact that it's defined as a "statement terminator" in the settings.

As a result of this undesired behaviour (bug) this means that even a simple batch like the following will fail:

DECLARE @test int;

SET @test = 1;

In SSMS, it would be like writing the following:

DECLARE @test int
GO
GO
SET @test = 1
GO

(2 GOs on purpose as DBeaver also treats a blank line as a statement batch separator.)

Instead, you'll need to change your preferences in Editors>SQL Editor>SQL Processing. The method I used to get normal T-SQL behaviour was:

• Delimiters > Statement delimiter: GO
• Delimiters > Ignore native delimiter: Enabled
• Delimiters > Blank line is statement delimiter: Disabled
• Delimiters > Remove tailing query delimiter: Enabled (Not sure this is relevant)

This meant the above script worked and an error in a second batch that @test wasn't defined, and the on-screen "intellisense" appeared to give correct behaviour too. Screenshot

Answer 2

Including example DDL and DML will make this much easier to solve. Consider: DECLARE @Table TABLE (Column1 INT, Column2 DATE, ...); INSERT INTO @Table (Column1, Column2, ...) VALUES (1, '2024-01-01', ...), (2, '2024-02-01', ...);

In addition, as mentioned in the comments it is poor practice to use [N]Varchar without a length.

I was unable to reproduce your problem, but consider:

DECLARE @TempTable TABLE(ArticleCode VARCHAR(30), ArticleName VARCHAR(50));

;WITH n AS (
SELECT 1 AS n
UNION ALL
SELECT n+1
 FROM n
 WHERE n < 1000
)

INSERT INTO @TempTable
SELECT 'ARTCODE' + CAST(n AS VARCHAR(20)), 'Name' + CAST(n AS VARCHAR(45))
 FROM n
 OPTION (MAXRECURSION 1000);

This generates a common table expression with 1000 rows, and then selects from it with your string concatenation to insert into a dummy table.

SQL doesn't fair well with loops, and they're best avoided when possible. The rule of thumb is if you find yourself using a cursor, you probably aren't doing it the best way.

Answer 3

You could just select from a system table eg syscomments and use rownumber() to greatly simplify and do a single insert. I believe concat was introduced in 2012 also.

insert into dbo.Articles(ArticleCode, ArticleName)
select Concat('ARTCODE', rn), Concat('Name', rn)
  from (
    select top(1000) Row_Number() over (order by (select null)) rn
    from master.dbo.syscomments
)t;