SELECT TOP problem

Question

Post reply

SELECT TOP problem

Jonathan Matt

SSCarpal Tunnel

Points: 4027
More actions
November 25, 2002 at 1:26 pm

#79188

Hello all,
I have a stored procedure that takes four input parameters:
CREATE PROCEDURE proc_FreeTextRankNotes
(@Top smallint,
@SearchText varchar(255),
@BeginDate varchar(20),
@EndDate varchar(20))
The select statement that uses @Top is
SELECT DISTINCT TOP @Top notescustlog.notes_key, KEY_Tbl.RANK
FROM notescustlog
INNER JOIN
FREETEXTTABLE (notescustlog, notes_text, @SearchText)
AS KEY_Tbl
ON notescustlog.notes_id = KEY_Tbl.
ORDER BY KEY_Tbl.RANK DESC
If I put an actual number like '20' the query works and I get the expected results. However if I refer to the @Top parameter I get a syntax error that I cannot seem to fix!
All the other parameters work as expected and are referenced the same way as @Top.
Any help appreciated!!
Jonathan

Viewing 8 posts - 1 through 7 (of 7 total)

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

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 1

You will need to use dynamic sql something like this:

declare @top int

declare @cmd varchar(1000)

set @top = 10

set @cmd = 'select top ' + cast(@top as char) + ' * from northwind.dbo.orders'

exec (@cmd)

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Jonathan Matt SSCarpal Tunnel Points: 4027 More actions · Answer 2

Greg,

What's different about passing in @Top as opposed to the other three parameters that would require dynamic SQL?

This sp is being called from an Access event procedure where the user supplys values for the four parameters. The text, begin date and end date are passed in no problem.

Thanks

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 3

I really don't have a good explaination, but I'm guessing it has to do with building an execution plan at compile time. If you try to use a variable in a place that is evaluated at runtime you have no problems (ie ... where x = @varaible). If the value of the variable is placed in your sql code in a place that needs to be evaluated at compile time then you get an error. Hope this helps.

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

dj_meier SSCrazy Points: 2732 More actions · Answer 4

If you want to change the number of rows returned each time by passing in a parameter, you will have to use SET ROWCOUNT instead of TOP. SET ROWCOUNT will accept a parameter for the number.



CREATE PROCEDURE proc_FreeTextRankNotes

(@Top smallint,

@SearchText varchar(255),

@BeginDate varchar(20),

@EndDate varchar(20))

SET ROWCOUNT @Top

SELECT DISTINCT notescustlog.notes_key, KEY_Tbl.RANK 

  FROM notescustlog 

  INNER JOIN 

  FREETEXTTABLE (notescustlog, notes_text, @SearchText)

  AS KEY_Tbl

  ON notescustlog.notes_id = KEY_Tbl.

  ORDER BY KEY_Tbl.RANK DESC

SET ROWCOUNT 0  --< This is optional here.

-Dan

Edited by - dj_meier on 11/25/2002 3:08:58 PM

-Dan

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 5

That IS a good explanation Greg.

Edited by - Scorpion_66 on 11/25/2002 4:38:56 PM

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 6

A small note that TOP is mroe performanct than ROWCOUNT as it is integrated in the optimiser, which supports Gregs view on why it can't take a variable. I agree with this thinking

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Jonathan Matt SSCarpal Tunnel Points: 4027 More actions · Answer 7

Finally had time to get back to it!

Thanks all for your help. SET ROWCOUNT is working perfectly and I now understand why TOP is different. SQL Server Central is the best!

Jonathan