0

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?

Improve this question
6
  • 2
    I can't replicate your problem, Commented Feb 22, 2024 at 17:18
  • 2
    Bad Habits to Kick : Declaring VARCHAR without (length) Commented Feb 22, 2024 at 17:18
  • post the complete error mesaage also 2012 is long past its prime Commented Feb 22, 2024 at 17:18
  • 4
    Perhaps the problem is DBeaver; it's a known bug/problem that it thinks that a semicolon (;) is a batch separator, not a statement terminator. Have you checked your settings? Are you really attached to DBeaver, or could you change to an IDE that doesn't have the "feature"? Commented Feb 22, 2024 at 17:19
  • 2
    2012 isn't just "past it's prime"... it's formally end of life and no longer supported, to the point of not even getting security updates for several years now, in spite of multiple known and active serious CVEs. That's a big deal, and upgrading this server should be job #1. Commented Feb 22, 2024 at 17:21

3 Answers 3

Reset to default
3

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

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

1 Comment

I had a feeling it was a problem with dbeaver and I guess I did the right think to add the tag. Your solution worked for me, thanks!
0

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.

Improve this answer

4 Comments

If the problem is DBeaver, then this is going to result in the same problem. Also, calling a table variable @TempTable is a somewhat misleading, it isn't a temp(orary) table.
"SQL doesn't fair well with loops, and they're best avoided when possible." Ironically, what you are doing here with the CTE, it is looping (recursing) through the (new) rows one at a time. A Tally would be a significantly more performant.
@ThomA Just followed the OPs name to keep things simple. Yes, the CTE is a recursion, that's patently obvious from the use of the MAXRECURSION option. A tally table would be preferable, and if the OP has the permissions to create a table that is a better way to go. The CTE is still preferable to 1000 batches, be that via a cursor, or repeated batches.
A tally doesn't need a table.
0

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;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.