SELECT TOP @variable + UNION problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT TOP @variable + UNION problem

Author

Topic

Kalle
Starting Member

4 Posts

Posted - 2005-06-28 : 11:40:33

I have two problems.
I want to do something like this: SELECT TOP @variable
I also want, in the same query, to combine two SELECT to one result.
This result should also be ordered by time desc.

Example:

DECLARE @nr1 int, @nr2 int

SELECT @nr1 = news_nr, @nr2 = press_nr
FROM User
WHERE id = 12345

SELECT TOP @nr1 id, title, time, type = 'News'
FROM News
ORDER BY time DESC
UNION
SELECT TOP @nr2 id, title, time, type = 'Press'
FROM Press
ORDER BY time DESC

Do anyone have any idea how to achive this on Microsoft SQL Server 2000?

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-28 : 11:56:27

Use rowcount with your variable.

declare @variable int
set @variable = 69

set rowcount @variable

select ...

set rowcount 0

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-28 : 11:59:13

Hi Kalle,
You can't do a SELECT TOP @variable statement. SQL Server doesn't allow you. You do have several other options though:
1. You could create a string variable containing your SQL statement and then execute that string. It tricks SQL Server into thinking the variable is actually a integer. Something like this:
DECLARE @varcommand varchar(200)
@varcommand="SELECT TOP " + @nr1 + " id, title, time, type = 'News'
FROM News...
Execute(@varcommand)
2. You could use rowcount to set the number of rows, e.g.:
set rowcount @nr1
However you'll have problems with that one if you want to do the UNION, unless you do it the way SeventhNight has been showing me here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51619.
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

Kalle
Starting Member

4 Posts

Posted - 2005-06-28 : 12:00:30

So how do I write that?
Including the union statement.
This is not correct code when I run in query analyser:

DECLARE @nr1 int, @nr2 int

SELECT @nr1 = news_nr, @nr2 = press_nr
FROM Usertable
WHERE id = 12345

SET rowcount @nr1
SELECT id, title, time, type = 1
FROM News
WHERE id = 12345

UNION

SET rowcount @nr2
SELECT id, title, time, type = 2
FROM Press
WHERE id = 12345

ORDER BY time DESC

SET rowcount 0

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-28 : 12:04:47

Look at SeventhNight's fifth post at the link I gave you above. He gives a detailed code example. :-)
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

Kalle
Starting Member

4 Posts

Posted - 2005-06-28 : 12:08:12

Yes, the only solution is probably to use a temporary table.
I do not want to use Execute(@varcommand) because this imply that the query have to be optimised everytime it executes, I think.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-28 : 12:15:40

Ex:

declare @nr1 int, 
	@nr2 int

declare @results table ([id] int, title varchar(100), [time] datetime, type int)

select 	@nr1 = isnull(news_nr,0), 
	@nr2 = isnull(press_nr,0)
from usertable
where id = 12345

set rowcount @nr1
insert into @results
	select 	[id], 
		title, 
		[time], 
		1
	from news
	where [id] = 12345
order by [time] desc


set rowcount @nr2
insert into @results
	select 	[id], 
		title, 
		[time], 
		2
	from press
	where id = 12345
order by [time] desc

set rowcount 0

-- return the results
select	[id], 
	title, 
	[time], 
	type 
from @results

Kalle
Starting Member

4 Posts

Posted - 2005-06-28 : 12:30:08

Thank you Nathans! I'll try to use your solution.
But is this the only (and the best) way to do this?

eralper
Yak Posting Veteran

66 Posts

Posted - 2005-06-28 : 19:29:30

Just a note on "SELECT TOP @variable"

We are able to use a variable after TOP in YUKON. But not in sql2k as mentioned in above replies to this topic

Eralper
http://www.kodyaz.com

-------------
Eralper
http://www.kodyaz.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources