@var = TOP(@var) in SQL 2000

Question

Post reply

@var = TOP(@var) in SQL 2000

johnsonl4

SSC Veteran

Points: 232
More actions
September 4, 2008 at 3:34 pm

#71967

This is in SQL 2000. I need to select top (some) records from a view into a variable. I use:
declare @lastzip varchar(8000)
--@lastzip value obtained from another 'select' stmt
select @zip = top (@lastzip) zipcode + ',' From vwSalesTeam v
where v.userid = @userid order by zipcode asc
I get an error
Msg 170, Level 15, State 1, Procedure usp_search_ver5, Line 386
Line 386: Incorrect syntax near '='.
I was working in SQL 2005. I would have used the above code in SQL 2005. But now I'm using 2000 and I'm getting this error.
Could anyone tell me what I'm doing wrong here???
Any ideas/Suggestions are highly appreciated.

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

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

johnsonl4 SSC Veteran Points: 232 More actions · Answer 1

🙁 I made a typo

declare @lastzip int is correct.

i typed wrongly as declare @lastzip varchar(8000)

John Rowan SSC Guru Points: 56440 More actions · Answer 2

TOP does not allow for a variable value in SQL Server 2000.

You'll need to use SET ROWCOUNT:

declare @mytable table (zipcode varchar(10))

insert into @mytable

select '68144' union all

select '68132' union all

select '68105'

DECLARE @throttle int,

@var varchar(2000)

SET @var = ''

SET @throttle = 2

SET ROWCOUNT @throttle

SELECT @var = @var + zipcode + ','

FROM @MyTable

SET ROWCOUNT 0

SELECT @var

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

johnsonl4 SSC Veteran Points: 232 More actions · Answer 3

Thanks for your reply John :). I see what you are telling

instead of TOP use ROWCOUNT.

initialize ROWCOUNT with the # of rows I need.

and once the job is done, set it to 0.

I'll give this a try and get back to you.

Thanks,

Vijeya Shobana

John Rowan SSC Guru Points: 56440 More actions · Answer 4

Yep, that's it. I should emphasize the importance of setting ROWCOUNT back to 0 (unlimiited) because if you don't, SQL Server will use the limit that you've set for the duration of your connection.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

johnsonl4 SSC Veteran Points: 232 More actions · Answer 5

wowww gr88 john 🙂 I got it. I spent a few hours browsing forums before posting here. I stumbled across 'rowcount' but didn't understand that. I didn't find good materials on this matter. I posted here as my last resort. Luckily you saw it and replied. You gave a good swift explanation on 'rowcount' too.

I got it working buddy :). Thanks once again and I appreciate your prompt response.

Thanks

Vijeya Shobana

John Rowan SSC Guru Points: 56440 More actions · Answer 6

No problem.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden