CODEWITHSUNDEEP
sql-server
Nick Sandel
Nick Sandel

Reputation: 112

SQL server SET variable not allowing option when using SELECT

I have found some odd syntax behaviour around the use of OPTION on a SELECT statement when assigning values to local variables. It looks like OPTION is allowed when the statement starts with SELECT but if it's part of a SET command then it is disallowed.

DECLARE @var CHAR(1)

SET @var = (SELECT '1' OPTION (QUERYTRACEON 2312))

SET @var = (SELECT '1') OPTION (QUERYTRACEON 2312);

SELECT @var = '1' OPTION (QUERYTRACEON 2312)

SELECT @var

Pasting the above into SSMS shows both varieties of SET are disallowed but the SELECT version is allowed. I would like to know if my syntax on SET is wrong and what the correct use for OPTION would be, or if this is simply the behaviour and I need to use SELECT to set the variable.

Upvotes: 1

Views: 266

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

OPTION Clause (Transact-SQL)

This clause can be specified in the SELECT, DELETE, UPDATE and MERGE statements.

Upvotes: 1

Related Questions