Incorrect syntax near '('. when using Select top (@param)

Question

Post reply

Incorrect syntax near '('. when using Select top (@param)

Patrick.I

Hall of Fame

Points: 3105
More actions
November 28, 2007 at 5:34 pm

#63121

When i run this code below in Query Anayzer SQL Server 2000
I get error
Incorrect syntax near '('.
Any ideas this seems to run on SQL SERVER 2005 is that right
declare @nrows as int
declare @term as varchar(50)
--set @nrows = 2
select @nrows = 25
set @term = '%pat%'
SELECT DISTINCT TOP(@nrows) FirstName FROM Employee WHERE FirstName like @term
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

This syntax was introduced in SQL2005.

In SQL2000 use SET ROWCOUNT @param instead.

_____________
Code for TallyGenerator

Patrick.I Hall of Fame Points: 3105 More actions · Answer 2

Thanks i actually solved it using dynamic query like below:

But now i want to concantenate the FirstName and the LastName

but want to use the parameter @sqlrest

but when i run the query i get error

Incorrect syntax near ' + E.LastName'.

declare @term as varchar(50)

declare @MyCount as varchar(2)

declare @sql as nvarchar(200)

declare @sqlrest as nvarchar(200)

set @sqlrest = 'E.FirstName + ' ' + E.LastName'

set @term = 'pat'

select @mycount = 6

--My Solution to the first pboblem

select @sql ='SELECT distinct top ' + @MyCount + ' FirstName FROM Employee

WHERE Firstname LIKE '''+ '%' + @term + '%' + ''''

--my problem now

select @sql ='SELECT distinct top ' + @MyCount + ' ' + @sqlrest + ' FROM Employee E

WHERE E.Firstname LIKE '''+ '%' + @term + '%' + ''''

exec sp_executesql @sql

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Always do

[font="Courier New"]PRINT @sql[/font]

before you do

[font="Courier New"]exec sp_executesql @sql[/font]

_____________
Code for TallyGenerator

Patrick.I Hall of Fame Points: 3105 More actions · Answer 4

I have tried that put nothing is printed out:crying:

Ian Yates SSCoach Points: 19738 More actions · Answer 5

Firstly, avoid dynamic SQL unless absolutely necessary. Someone gave you an answer that didn't need dyn sql - I suggest you use it.

But...

Your problem is that you have a syntax error in the SQL string that you eventually build.

Try

set @sqlrest = ', E.FirstName + ', ' + E.LastName'

instead - you want a comma between the columns you are selecting. Note that there are two commas in the above text - one at the start and one in the middle.

Patrick.I Hall of Fame Points: 3105 More actions · Answer 6

Thanks and normally i won't go for dynamic sql.

I want to use it for my Ajax part of my application which is an auto completebox

But when i tried your suggestion i get error :-

Incorrect syntax near ','.

on the line

set @sqlrest = ', E.FirstName + ', ' + E.LastName'

Any ideas

Sergiy SSC Guru Points: 110208 More actions · Answer 7

[Code]select @sql ='SELECT distinct top ' + @MyCount + ' ' + @sqlrest + ' FROM Employee E

WHERE E.Firstname LIKE '''+ '%' + @term + '%' + ''''

print @sql

[/Code]

Is it what you tried?

_____________
Code for TallyGenerator

Patrick.I Hall of Fame Points: 3105 More actions · Answer 8

Yes and getting confused now..

Still get the error:

Incorrect syntax near ','.

on line

set @sqlrest = ', E.FirstName + ',' + E.LastName'

declare @term as varchar(50)

declare @MyCount as varchar(2)

declare @sql as nvarchar(200)

declare @sqlrest as nvarchar(200)

set @sqlrest = ', E.FirstName + ',' + E.LastName'

set @term = 'pat'

select @mycount = 6

select @sql ='SELECT distinct top ' + @MyCount + ' ' + @sqlrest + ' FROM Employee E WHERE E.Firstname LIKE '''+ '%' + @term + '%' + ''''

PRINT @sql

Patrick.I Hall of Fame Points: 3105 More actions · Answer 9

Well i got it working

this did it

set @sqlrest = 'E.FirstName' +' , '+ 'E.LastName'

Giri Duddu SSCommitted Points: 1638 More actions · Answer 10

better u build the querry in ur application and run/ execute from there

Patrick.I Hall of Fame Points: 3105 More actions · Answer 11

Why are you prosposing this.

I'm thinking of writing a Store proc for this..

Any reason why thanks..