Querry With Dynamic Top records

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 2005 Forums

Transact-SQL (2005)

Querry With Dynamic Top records

Author

Topic

paulnamroud
Starting Member

26 Posts

Posted - 2010-06-10 : 10:40:18

Hi,

How can i return the result of a querry with a Dynamic Top xxx Records ???

create procedure [dbo].[pr_get_top_orders]
(
@p_number_order int
) as

Begin

Select Top @p_number_order *
From orders
Where total_amount > 10000
End

Thank you

Paul

Sachin.Nand

2937 Posts

Posted - 2010-06-10 : 10:42:16

Really you need to tell us the schema of your table if you want to get any help.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-10 : 10:44:41

quote:
Originally posted by paulnamroud

Hi,

How can i return the result of a querry with a Dynamic Top xxx Records ???

create procedure [dbo].[pr_get_top_orders]
(
@p_number_order int
) as

Begin

Select Top @p_number_order *
From orders
Where total_amount > 10000
End

Thank you

Paul

Dynamic SQL

declare @p_number_order varchar(5)
declare @sql varchar(1000)
set @p_number_order = '10'

set @sql = 'Select Top ' + @p_number_order  + ' * From orders Where total_amount > 10000' 

exec(@sql)

paulnamroud
Starting Member

26 Posts

Posted - 2010-06-10 : 10:45:06

Here's the real output:

create procedure [dbo].[pr_get_top_orders]
(
@p_number_order int
) as

Begin

Select Top @p_number_order order_id, customer_id, order_date, total_amount
From orders
Where total_amount > 10000
End

Paul

Sachin.Nand

2937 Posts

Posted - 2010-06-10 : 10:48:50

quote:
Originally posted by vijayisonly

quote:
Originally posted by paulnamroud

Hi,

How can i return the result of a querry with a Dynamic Top xxx Records ???

create procedure [dbo].[pr_get_top_orders]
(
@p_number_order int
) as

Begin

Select Top @p_number_order *
From orders
Where total_amount > 10000
End

Thank you

Paul

Dynamic SQL
declare @p_number_order varchar(5)
declare @sql varchar(1000)
set @p_number_order = '10'

set @sql = 'Select Top ' + @p_number_order + ' * From orders Where total_amount > 10000'

exec(@sql)

But the output from above query wont make any sense as everytime it will be different if there is no order by clause in the query provided the OP does not have a column that already sorts the records physically.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

paulnamroud
Starting Member

26 Posts

Posted - 2010-06-10 : 10:48:55

Thank you vijayisonly

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-06-10 : 10:51:59

Here is some nice horrible code. Hopefully someone will clean it up (or better yet, do it right).

create procedure [dbo].[pr_get_top_orders]
(
@p_number_order int
) as

BEGIN

SELECT 
	<column list here>
FROM
(
SELECT  
	* ,ROW_NUMBER() OVER(ORDER BY <order column name here>) AS foo 
FROM 
	orders
WHERE total_amount > 10000
) a
WHERE 
	foo <= @p_number_order
END

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-10 : 11:20:59

Erm.....

In 2005 and later you can do stuff like this:


DECLARE @foo TABLE (
	[ID] INT IDENTITY(1,1) PRIMARY KEY
	, [value] VARCHAR(255)
	)

-- put some random data into @foo
INSERT @foo ([value])
      SELECT 'a#' + CAST(NEWID() AS VARCHAR(255))
FROM
	sys.objects

-- show all the records
SELECT * FROM @foo

-- Variable for the top N records
DECLARE @topRec INT SET @topRec = 10

-- select em.
SELECT TOP (@topRec) [value] FROM @foo ORDER BY [ID]

So there is no need for ROW_NUMBER() or dynamic SQL I think.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-10 : 11:23:14

Paul -- I think your proc needs to be only this:



create procedure [dbo].[pr_get_top_orders]
(
@p_number_order int
) as 

Begin

Select Top (@p_number_order) <COLUMN LIST HERE>
From orders
Where total_amount > 10000
End

Note the brackets round the @p_number_order.

Also -- stop doing SELECT *

It's very lazy and can lead to performance problems if you are bringing back more columns than you are using.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-06-11 : 07:50:36

Nice. See, i knew there was a better way.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources