I have a query like so:
SELECT id, @RowNumber = ROW_NUMBER() OVER (ORDER BY id) as 'number'
FROM WaitList
WHERE email = @Email
However I am getting error:
Incorrect syntax near the keyword 'as'.
What I am trying to do is assign the ROW_NUMBER() to a variable.
I really need to get the row number from the WaitList table.
Could I put the results in variable table and then get the row number by selecting 'number'?
If you want the number of the row that has the matching email address, but the table doesn't actually HAVE row numbers, then you should create a CTE to assign row numbers, and then select the specific row from that... something like this:
WITH NumberedRows AS (
SELECT [id], [email], ROW_NUMBER() OVER (ORDER BY id) as [Number]
FROM WaitList
)
SELECT @RowNumber = Number
FROM NumberedRows
WHERE email = @Email
Note that if there are SELECT statements prior to this, you'll need to make sure you terminate the SELECT statement (or other SQL Statements) with a semi-colon. OR, if you wish, you could just prefix the WITH with a semi-colon, like ";WITH..."
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Your code looks like SQL Server. You cannot assign a variable and return a result set with the same query:
SELECT id, ROW_NUMBER() OVER (ORDER BY id) as number
FROM WaitList
WHERE email = @Email;
Assigning the value to a variable doesn't really make sense. If you want, say, the total number of matching rows, then use aggregation functions:
select @cnt = count(*)
from waitlist
where email = @email;
select a.id , a.number
from
(SELECT id, ROW_NUMBER() OVER (ORDER BY id) as number
FROM WaitList
WHERE email = @Email) a
SELECT id, ROW_NUMBER() OVER ( ORDER BY id) as number
FROM WaitList
WHERE email = @Email;
