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.
Yes, you can assign the value of variable inside if.
You can't use a variable in an IN clause - you need to use dynamic SQL, or use a function (TSQL or CLR) to convert the list of values into a table. Save this answer.
Java allows you to declare variables within the body of a while or if statement, but it's important to remember the following: A variable is available only from its declaration down to the end of the braces in which it is declared. This region of the program text where the variable is valid is called its scope .
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.
Rather than doing IF EXISTS
, you could just do
DECLARE @myvar int SELECT @myvar = theTable.varIWant.....; IF @myvar IS NULL BEGIN...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With