1

I'm trying to use the same scalar variables for more than one query, but during the execution of the second query, I get the error "Must declare scalar variable @new_serial".

Here is my code:

BEGIN TRANSACTION T1;

DECLARE @refDoc1 CHAR(10)
DECLARE @refDoc2 CHAR(10)
DECLARE @docAmount DECIMAL(18,4)
DECLARE @docIvaAmount DECIMAL(18,4)
DECLARE @new_serial CHAR(10)

SET @refDoc1 = '0000671853'
SET @refDoc2 = '0000682341'
SET @docAmount = 9.38
SET @docIvaAmount = (SELECT @docAmount * 0.22)
SET @new_serial = (SELECT format(autonum + 1, '0000000000') FROM serialtable WHERE tablecode = 'prog\MYTABLE')

SELECT
    @new_serial,
    pncodute,
    pncodcau,
    @docAmount
FROM mytable
WHERE pnserial = @refDoc1
;

SELECT
    @new_serial,
    pncodute,
    pncodcau,
    @docAmount
FROM mytable
WHERE pnserial = @refDoc2
;

ROLLBACK TRANSACTION T1;

I've tried to include the queries inside a single transaction, but I still get the error.

I read several guides about scalar variables and my example doesn't seem to me one of the cases where the scalar variables are out of scope in the second query.

Thank you in advance.

Ma3x
  • 516
  • 6
  • 19
  • 3
    How are you executing these queries? If you try running this batch in Management Studio (as a *whole batch*, *not* by executing parts of it), I would indeed not expect any trouble. Other software may be proactively splitting statements in a way that breaks things. – Jeroen Mostert Sep 14 '22 at 12:20
  • 3
    Aside from the issue at hand you are creating another issue for yourself. You have created a race condition here when trying to create your own incrementing number. You should use an identity or a sequence instead. – Sean Lange Sep 14 '22 at 12:23
  • @jeroenMostert I'm simply executing this code as a script in DBeaver client. – Ma3x Sep 14 '22 at 12:28
  • @SeanLange I know it's awful, but the DB architecture is fixed and I can't touch it. And all tables ID are related to that ```serialtable``` which contains the last used ID for each table... – Ma3x Sep 14 '22 at 12:30
  • 2
    Do you, by any chance, have semicolons (`;`) as a batch separator, rather than a statement terminator, in DBeaver? – Thom A Sep 14 '22 at 12:30
  • @Larnu Naaaaaaa.... you got it! I would never have thought that a client would consider the semicolon as script separator and the blank line as statement separator...never! Thank you very much, mate – Ma3x Sep 14 '22 at 12:39
  • @Larnu Also consider that the "Editors -> SQL Editor -> SQL Processing -> Statements delimiter" is set to semicolon (and "Ignore native delimiter" is unchecked)...but I still wanted to try removing the semicolons at the end of each query and it worked... – Ma3x Sep 14 '22 at 12:43
  • Hi @Ma3x. Why do you use or prefer DBeaver instead Sql Management Studio ? – pfigueredo Sep 14 '22 at 12:48
  • @pfigueredo 'Cause I work on Mac OS :) Moreover, I have to work on MySQL, Postgresql, SQL Server... DBeaver, at this time, for me is the perfect IDE "to rule them all" ;) – Ma3x Sep 14 '22 at 13:04

0 Answers0