Assigning a variable inside an IF EXISTS clause

Question

Trying to assign a variable inside an if exists clause for TSQL

DECLARE @myvar  int

IF EXISTS (SELECT @myvar = theTable.varIWant..... )

I thought this would work, but apparently not? Or perhaps (more likely) I'm doing it wrong.

Solution 1

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.

Solution 2

Rather than doing IF EXISTS, you could just do

DECLARE @myvar  int
SELECT @myvar = theTable.varIWant.....;
IF @myvar IS NULL
BEGIN...

Solution 3

It will not work just because in EXISTS construction sql server just validates if any row exists and it does not matter the select-columns or assignment section. This is done for optimizing the performance.

Comments

[Kambo_Rambo]

Ok my simpler workaround which I use is SELECT @myvar =.... and then do an IF (@myvar <> null). This solves my problem and even makes it more logical but Im still interested to know If I can assign it.

[Sparky]

My guess is no, since the assignment statement doesn't return a boolean value that satisfies IF EXISTS

[Jeff.Clark]

Hmm, which is semantically better. This or Michael Petter's answer?

[Andriy M]

@Jeff.Clark: Semantically – not sure. Mine makes perfect sense to me and I don't see any problems, from the logical standpoint, with the option proposed by Michael Petter either. There is one potential issue of a different kind with his solution, and I've expressed that in my answer. (See the paragraph where I'm discussing the OP's workaround, which is basically the same as Michael's suggestion.)