CODEWITHSUNDEEP
sql-serverselectsubquery
Dodgeball
Dodgeball

Reputation: 125

Incorrect syntax near the keyword 'WITH'

I have a problem storing the result of the query inside a variable using with query.

An error shows that there's something wrong near WITH and then i tried to place a semi colon (;) before with

here's my code

        SET @long2 =  WITH QUERY AS (
                       SELECT LONG, ROW_NUMBER() OVER(ORDER BY philtime desc) AS RowNumber
                       FROM TblCurrent_ROI where KMRUN IS NULL and LONG is not null and ACCOUNT = @account
                       )
                  SELECT LONG FROM QUERY WHERE ROWNUMBER = 2

Upvotes: 0

Views: 118

Answers (1)

qxg
qxg

Reputation: 7036

WITH QUERY AS (
    SELECT LONG, ROW_NUMBER() OVER(ORDER BY philtime desc) AS RowNumber
    FROM TblCurrent_ROI where KMRUN IS NULL and LONG is not null and ACCOUNT = @account
)
SELECT @long2 = LONG FROM QUERY WHERE ROWNUMBER = 2

Upvotes: 1

Related Questions