CODEWITHSUNDEEP
sql-servertriggerssql-server-2012
BoKu
BoKu

Reputation: 67

In MSSQL when do you use the @ symbol and when do you not?

In MSSQL I know you declare and set variables like this for example

DECLARE @CONTNUMBER VARCHAR(10)
SET @CONTNUMBER= 'ABC1234567'


However, I've come across this issue when using a trigger and setting a variable in the select statement.

SELECT SMS.VEHICLE, @CONTNUMBER = SMS.CONTNO, SMS.ITEMNO, SMS.[STATUS], ...

enter image description here


But the error message disappears when I omit the leading @ symbol

SELECT SMS.VEHICLE, CONTNUMBER = SMS.CONTNO, SMS.ITEMNO, SMS.[STATUS] ...

enter image description here

So my question is, when do I use and when do I omit the @ symbol when using variables?

TYIA

Upvotes: 0

Views: 726

Answers (2)

Liesel
Liesel

Reputation: 2979

As @TomTom mentions, you can't assign a variable and return a result set in the same SELECT.

In your code, SELECT CONTNUMBER = SMS.CONTNO is the same as SELECT SMS.CONTNO AS CONTNUMBER - you're just aliasing the output column. This is covered here: https://msdn.microsoft.com/en-us/library/ms176104.aspx

Also, variables are covered here https://msdn.microsoft.com/en-us/library/ff848809.aspx

Upvotes: 1

TomTom
TomTom

Reputation: 62101

You use the @ when you reference a variable.

The error is not the @ - it is mixing @ and not @ output.

If you bother reading the error message it tells you so. You can not have assignments (@variable =) and data retrieval (SMS.Vehicle into the output) within one statement.

Upvotes: 1

Related Questions