0

Assume @Ref <> 0 in first condition. Therefore my code will not reach the declaration of @XYZ (line 2).

I think it must raise an error in the second IF because of @XZY was not declared.

But I'm surprised that there is no error raised.

IF @Ref = 0 
BEGIN
    DECLARE @XYZ int

    SELECT @XYZ = RISKGROUP
    FROM POLCONT WITH (NOLOCK, NOWAIT) 
    WHERE CONT = 555
END

IF @RISKGROUP <> @XYZ  
BEGIN
    -- do something ...
END

For example.

In python it raises this error:

Error: local variable 'XYZ' referenced before assignment

Dale K
  • 25,246
  • 15
  • 42
  • 71
Jora Karyan
  • 66
  • 1
  • 9
  • 1
    what is RISKGROUP ? what is POLCONT ? what is CONT ? We cannot see your screen, you have to provide us with enough information. I am guessing you need something like `select @XYZ = riskgroup from polcount ...` – GuidoG Apr 07 '22 at 06:12
  • 1
    And when you declare a variable, you also need to provide its type, for example `declare @xyz int` – GuidoG Apr 07 '22 at 06:14
  • Thank you bro, I have already edited it. Here the variables were only examples. The main question is 'Why there is no error here' ``` if @RISKGROUP <> @XYZ begin -- do something ... end ``` – Jora Karyan Apr 07 '22 at 06:20
  • 2
    Does [this](https://stackoverflow.com/questions/2952553/why-is-variable-declared-inside-if-statement-created-even-when-condition-evaluat) answer your question? – Zhorov Apr 07 '22 at 06:30
  • Stop splattering your code with [nolock](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere). And stop assuming a "bro" helped. – SMor Apr 07 '22 at 10:58

1 Answers1

0

You need to declare your variable with a type before you can use it

declare @xyz int -- assuming the datatype of column riskgroup is also int

select @xyz = riskgroup
from   polcont
where  polcont = 555

EDIT

You altered your question since I have posted my answer, now your problem is you declare the variable in the wrong scope.

DECLARE @XYZ int

if @Ref = 0 
begin 
   SELECT @XYZ = RISKGROUP
   FROM   POLCONT --WITH (NOLOCK, NOWAIT) don't do nolock everywhere !
   WHERE  CONT = 555
end

if @RISKGROUP <> @XYZ  
begin
   -- do something ...
end

EDIT 2

As to your question why it does not give any error, that is strange indeed

See this DBFiddle I would indeed expect an error, but it somehow does not

This is explained in the documents as normal per design, though in my mind it is a flaw.
Also see this Question about the same subject

EDIT 3

So it seems that is does not matter much in TSQL where you declare your variables, but as a programmer I find this looking weird, so I prefer to put my variables in what is for me the correct scope.

GuidoG
  • 11,359
  • 6
  • 44
  • 79
  • No bro, in my example the declaration under the first IF statement. Is it wrong idea? – Jora Karyan Apr 07 '22 at 06:24
  • Yes, always declare variables in the correct scope, make sure it is visible where needed. I always declare all variables at the top of my procedures – GuidoG Apr 07 '22 at 06:27
  • Ok, Thanks a lot. It's very important for me. But it is interesting that there is no warning/error from SQL in my wrong declaration statement. Doesn't it? – Jora Karyan Apr 07 '22 at 06:29
  • I agree, I tried it and it seems to work without error, see [this](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=df87247a6d34436dc7713060952b39de) – GuidoG Apr 07 '22 at 06:30
  • That is why we are Pythoners. – Jora Karyan Apr 07 '22 at 06:31
  • Although not documented as such, there are no conditional declarations: `if DatePart( second, GetDate() ) % 2 = 1 declare @Foo as Int else declare @Foo as BigInt;`. – HABO Apr 07 '22 at 13:26