question

jaijune avatar image
jaijune asked

Incorrect syntax near '@n'

Hi friends, i am new to the sql, ------------------------------------------------------------------------------------ my query is to find the nth highest salary, the problem is, i pass the argument(@n) to the query, when is execute this stored procedure am getting an "Incorrect syntax near '@n' " -------------------------------------------------------------------------------------- create procedure proc_nthsalary (@n int) as begin select top 1 name, salary from emp_details where salary in(select distinct top @n salary from emp_details order by salary desc) order by salary end -------------------------------------------------------------------------------- am passing argument to this query, it works fine, then why the above stored procedure throws an error ------------------------------------------------------------------------------- create procedure proc_getname (@n salary) as begin select name from emp_details where salary=@salary end
syntax
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
KenJ avatar image
KenJ answered
Add parentheses around the variable in the TOP clause `(select distinct top (@n) salary from emp_details order by salary desc` Here is the Books Online topic - http://msdn.microsoft.com/en-us/library/ms189463.aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jaijune avatar image jaijune commented ·
Mr. ken, as per your instruction i added parenthesis, whereas still throws an error create procedure proc_nthsalary (@n int) AS BEGIN select top 1 name, salary from emp_details where salary in(select distinct top (@n) salary from emp_details order by salary desc) order by salary END
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Pretty sure @Ken has it right. I tested it with this: CREATE PROCEDURE proc_nthsalary (@n INT) AS BEGIN SELECT TOP 1 eph.PayFrequency, eph.Rate FROM HumanResources.EmployeePayHistory AS eph WHERE eph.Rate IN (SELECT DISTINCT TOP (@n) eph2.rate FROM HumanResources.EmployeePayHistory AS eph2 ORDER BY eph2.Rate DESC) ORDER BY eph.Rate END Had to change stuff to use a table in Adventureworks, but it's the same as yours. Just needed the parens to fix it up.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.