Assigning a Variable Inside an If Exists Clause

Assigning a variable inside an IF EXISTS clause

In my installation of SQL Server 2008 R2, it simply doesn't compile. The parser complains about there being incorrect syntax near =.

I believe it must have something to do with mixing value assignment and data retrieval in a single SELECT statement, which is not allowed in SQL Server: you can have either one or the other. Since, when you assign values, the row set is not returned but the EXISTS predicate expects it to be, the assignment cannot be allowed in that context, so, to avoid confusion, perhaps, the limitation must have been imposed explicitly.

Your workaround, which you are talking about in a comment, is a decent one, but might not work well somewhere in the middle of a batch when the variable has already got a value before the assignment. So I would probably use this workaround instead:

SELECT @myvar = ...
IF @@ROWCOUNT > 0 ...

As per MSDN, the @@ROWCOUNT system function returns the number of rows read by the query.

SQL variable in IF exists

You might want to use SELECT INTO:

select lend_id into @lennuid
from lend 
where sihtpunkt=in_kuhu AND kuupaev=in_date;

Alternatively:

select @lennuid := lend_id
from lend 
where sihtpunkt=in_kuhu AND kuupaev=in_date;

Either way works the same, then you can use that variable however you like...

In your particular example, though, the problem is actually how your exists is being used along with the set. To fix this, use the following:

IF exists (select lend_id from lend where sihtpunkt=in_kuhu AND kuupaev=in_date) THEN ...

What the problem here is, is you were setting a variable with SET inside of the EXISTS check.

Or you could do your select first as a COUNT into a numeric variable:

DECLARE cnt INTEGER;
select Count(lend_id) into @cnt 
from lend 
where sihtpunkt=in_kuhu 
   and kuupaev=in_date;

IF @cnt > 0 THEN

END IF;

Assign variable value inside if-statement

Variables can be assigned but not declared inside the conditional statement:

int v;
if((v = someMethod()) != 0) return true;

MySQL: SET Variable in if exists condition

13.2.10.6 Subqueries with EXISTS or NOT EXISTS

...

Traditionally, an EXISTS subquery starts with SELECT *, but it
could begin with SELECT 5 or SELECT column1 or anything at all. MySQL
ignores the SELECT list in such a subquery, so it makes no difference.

...

An option:

...
SELECT
  @AccountVerified := AccountVerified,
  @IsActive := IsActive,
  @Exists := TRUE
FROM tblUserLookUp,
     (SELECT
       @AccountVerified := NULL,
       @IsActive := NULL,
       @Exists := FALSE) `init`
WHERE UserName = inUserName;

IF (@Exists) THEN
  ...
ELSE
  ...
END IF;
...

Using a table variable inside of a exists statement

This will work:

[@BugRep].BUGCODE

You'll also need to change "b.CODE" to "b.BUGCODE" by the way ;)

Can you store a variable inside a if-clause?

This is the closest you can get:

Bar bar;
if(foo!=null && (bar = foo.getBar())!=null){
  System.out.println("Success: " + bar);
} else {
  System.out.println("Failiure.");
}

How to set multiple values inside an if else statement?

If you have more than one statement in a if condition, you must use the BEGIN ... END block to encapsulate them.

IF ((SELECT COUNT(*) FROM table WHERE table.Date > '2016-03-20') > 10)
BEGIN
 SET @test1 = 'test1'
 SET @test2 = 'test2' 
END
ELSE
BEGIN
 SET @test1 = 'testelse'
 SET @test2 = 'testelse'
END

T-SQL Set Variable IF record exists

One method in T-SQL is:

declare @Threshold float;

if (exists (select top 1 * from [DB]..[table1] P where P.RowId IS NOT NULL))
begin
    select @Threshold = [Value] 
    from [DB]..[table1]
end;
else select @Threshold = 0.99;

Personally, I think that is rather verbose. You can just do:

declare @Threshold float;

select @Threshold = coalesce(max([Value]), 0.99)
from [DB]..[table1]
where P.RowId is not null;

This works because an aggregation query without a group by always returns one row. If nothing matches, then the columns are NULL.

This is not 100% equivalent, because a row could exist with a NULL value. This version would assign 0.99 rather than NULL (as yours does). I suspect that this behavior may be what you really want.

Assigning a variable conditionally inside a SELECT statement on SQL SERVER

You can't assign in the WHERE clause. You can in the SELECT clause:

DECLARE @tmpvar varchar(5)
....

SELECT @tmpvar =
      CASE WHEN (some condition here)
             THEN 'YES'
             ELSE 'NO'
      END,
....
FROM some_table
WHERE 
....

But only if you're not also attempting data retrieval at the same time. Also, it's pretty poorly defined which row(s) will influence the result if there's more than 1 row in the result set, unless you have an ORDER BY clause:

If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.

---

As you may have noticed, SQL uses = for assignment, comparison and introducing aliases. Which one is determined by the position that the = appears in. In the WHERE clause, = can only be comparison.

---

Related Topics