CODEWITHSUNDEEP
sqlsql-serversql-server-2008
FrenkyB
FrenkyB

Reputation: 7207

Use SET from SELECT statement doesn't work

Why can I not set variable to select query result? I am receiving an error

Incorrect syntax near 'tbl'

Code:

DECLARE @AgentNumber INT = 2428
DECLARE @Node HIERARCHYID

SET @Node =  -- this is throwing me syntax error
    (SELECT Node 
     FROM CM.ERGO_HIERARCHY
     WHERE AgentNumber = @AgentNumber) tbl

I know that below code works, but why I can not simply use SET ?

SELECT @Node = Node    -- that works
FROM
    (SELECT Node 
     FROM CM.ERGO_HIERARCHY
     WHERE AgentNumber = @AgentNumber) tbl

Upvotes: 0

Views: 103

Answers (1)

Renato Reyes
Renato Reyes

Reputation: 199

Try instead: 

SET @Node = 
(
SELECT TOP 1 Node 
FROM CM.ERGO_HIERARCHY
WHERE AgentNumber = @AgentNumber
)

Upvotes: 1

Related Questions