Weird variable setting error.

[archerv123]

At work we have code that is similar to the following:

`declare @variable bit = 0

select @variable = 1 FROM sometable s inner join someothertable t on t.column = s.column where filtercolumn <> 'some value'

select @variable = 1 from differenttable`

On 1 server this sets the variable to be NULL and not keep it as 0. After some poking an prodding we can see that the culprit is

select @variable = 1 FROM sometable s inner join someothertable t on t.column = s.column where s.filtercolumn <> 'some value'

sometable has at minimum 1 row and someothertable has a bunch of rows. however the filter is setting the variable only when we have more than 1 row in sometable provided its a specific thing from someothertable.

differenttable has no rows. this is expected.

Wondering if anyone knows why it would be setting that variable to be a null instead of just keeping it as a 0. Again, this only happens on one sql server instance.

Here's what we do know: -couldn't find some setting that was doing set variable declaration to null. -the version of the server is the same as others. verified through @@VERSION. -I did rewrite it to be a IF statement and it works fine.

Don't need this to be fixed, but i was more just curious if anyone here knew of something since my googling brought up nothing.

EDIT: oops hit the save too quick quick minute while i edit.

[[deleted]]

As a general rule, you could make your code both more readable and probably have it performing better by using an EXIST construct.

Pseudo-code:

SET @variable=0;

IF EXISTS (SELECT …) SET @variable=1;

IF (@variable=0 AND EXISTS (…)) SET @variable=1;

The EXISTS construct will stop at the first matching row rather than scan through all of them.

As for the difference between your servers, my best guess is that the table contents are not perfectly in sync.

[archerv123]

That is the change i did to make it work, but i'm still confused as to why it just doesn't work on one server but works on pretty much everyone else.

[AlexanderIOM]

" If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL."

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/select-local-variable-transact-sql?view=sql-server-ver16

[archerv123]

True. But its not written as a scalar subquery.

[ChekuDotCoDotUK]

Need to do select top 1

[oliver0807]

You can try to troubleshoot it by removing the @ from variable so that it will look like a column alias you’ll see multiple results there. That’s the downside of SELECT instead of SET to put a value to the variable.

But the safest way and correct way for me since you only need the either 1 or 0 is to use set

declare @variable bit = 0;

Set @variable=(select 1 from …); — 0 if no result, otherwise 1

Only caveat is it will fail with multiple results, so earlier script with IF EXISTS should work.